CODEWITHSUNDEEP
sql-serversql-server-2008
user3991213
user3991213

Reputation:

How to set default for a column using alter in SQL Server?

I am stuck at defining a default value to a already created column.

I created a column and didn't set a default in it but later I realized that I should have set default in it using alter. So I tried but failed searched every where but not found the solution.

Here is the code

create table stock
( 
    item_name varchar(200),
    item_code varchar(50),
    Price int,
    availabilty varchar(20),

    constraint i_n unique(item_name),
    constraint i_c primary key(item_code)
)

Now after adding some data into it using insert into I am not able to add default constraint in price column.

Help me please how to add default constraint in price column using alter

IF I add a constraint like this 

alter table stock
add constraint ID default '' (price)

ssms detect it as a error and says

Msg 142, Level 15, State 2, Line 0
Incorrect syntax for definition of the 'TABLE' constraint.

I am using SQL Server 2008

Thank you

Upvotes: 2

Views: 274

Answers (1)

marc_s
marc_s

Reputation: 755451

You need to use this:

ALTER TABLE dbo.Stock
ADD CONSTRAINT DF_Stock_Price DEFAULT(0) FOR Price

Since Price is an INT column, you cannot define '' as its default - it has to be a valid INT value!

Upvotes: 3

Related Questions