What is correct syntax for an ALTER
statement to add a default value to an existing column?
I can add new column with no errors:
ALTER TABLE tb_TableName ADD Record_Status varchar(20)
But If I try to alter existing column to apply default value by using the following statement:
ALTER TABLE tb_TableName
ALTER COLUMN Record_Status VARCHAR(20) NOT NULL DEFAULT ''
or
ALTER TABLE tb_TableName
ALTER Record_Status VARCHAR(20) NOT NULL DEFAULT ''
I have get an error:
Incorrect syntax near 'Record_Status'.
This question is related to
sql
sql-server-2008
Try this one.
ALTER TABLE tb_TableName
ALTER COLUMN Record_Status VARCHAR(20) NOT NULL
ALTER TABLE tb_TableName
ADD CONSTRAINT DEF_Name DEFAULT '' FOR Record_Status
To set a default value to a column, try this:
ALTER TABLE tb_TableName
ALTER COLUMN Record_Status SET DEFAULT 'default value'
Source: Stackoverflow.com