How to add default value in an existing table in sql server

Question

I am trying to add default value to an existing table, but it is not working at all. I am expecting to see 'NO' when I add data in my table but it is not happening. I am not sure what is going on or if my syntax is wrong here. Please help. Thanks

ALTER TABLE [myTable] 
ADD  CONSTRAINT [DF_myTable_FLAG]  DEFAULT ('NO') FOR [FLAG]

The datatype for this column is varchar.

Can you show us the insert statement you're using to insert a row? — meziantou
– meziantou, Commented Feb 11, 2014 at 21:42
First of all, this only applies to new rows being added, and it only works if you don't explicitly specify a value for that column in your INSERT statement. The syntax looks fine - but how are you inserting your new rows? — marc_s
– marc_s, Commented Feb 11, 2014 at 22:04

Dave C · Accepted Answer · 2014-02-11 21:44:32Z

3

Only new records will get the default.

Test this:

CREATE TABLE #TEST(NAME VARCHAR(5), FLAG VARCHAR(3))
INSERT INTO #TEST (NAME) SELECT 'DAVE'
ALTER TABLE #TEST ADD  CONSTRAINT [DF_TEST_FLAG]  DEFAULT ('NO') FOR [FLAG]
INSERT INTO #TEST (NAME) SELECT 'MOE'

SELECT * FROM #TEST

Old records must be manually updated.

answered Feb 11, 2014 at 21:44

Dave C

7,4821 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add default value in an existing table in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related