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.

• meziantou: Can you show us the insert statement you're using to insert a row?
• marc_s: 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?

Answer 1

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.