本文是小编为大家收集整理的关于SQL Server中的Alter列的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
ALTER语句的正确语法是什么是在现有列中添加默认值?
我可以添加没有错误的新列:
ALTER TABLE tb_TableName ADD Record_Status varchar(20)
但是，如果我尝试通过使用以下语句使用现有列以应用默认值:
ALTER TABLE tb_TableName ALTER COLUMN Record_Status VARCHAR(20) NOT NULL DEFAULT ''
或
ALTER TABLE tb_TableName ALTER Record_Status VARCHAR(20) NOT NULL DEFAULT ''
我有一个错误:
" record_status"附近的错误语法.
推荐答案
我认为您想要此语法:
ALTER TABLE tb_TableName add constraint cnt_Record_Status Default '' for Record_Status
基于您的某些评论，我猜想您的表中可能已经有null值，这会导致列更改为not null失败.如果是这种情况，那么您应该首先运行UPDATE.您的脚本将是:
update tb_TableName set Record_Status = '' where Record_Status is null ALTER TABLE tb_TableName ALTER COLUMN Record_Status VARCHAR(20) NOT NULL ALTER TABLE tb_TableName ADD CONSTRAINT DEF_Name DEFAULT '' FOR Record_Status
请参阅 sql带有演示的SQL小提琴
其他推荐答案
尝试这个.
ALTER TABLE tb_TableName ALTER COLUMN Record_Status VARCHAR(20) NOT NULL ALTER TABLE tb_TableName ADD CONSTRAINT DEF_Name DEFAULT '' FOR Record_Status
其他推荐答案
要将默认值设置为列，请尝试以下操作:
ALTER TABLE tb_TableName ALTER COLUMN Record_Status SET DEFAULT 'default value'
问题描述
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'.
推荐答案
I think you want this syntax:
ALTER TABLE tb_TableName add constraint cnt_Record_Status Default '' for Record_Status
Based on some of your comments, I am going to guess that you might already have null values in your table which is causing the alter of the column to not null to fail. If that is the case, then you should run an UPDATE first. Your script will be:
update tb_TableName set Record_Status = '' where Record_Status is null ALTER TABLE tb_TableName ALTER COLUMN Record_Status VARCHAR(20) NOT NULL ALTER TABLE tb_TableName ADD CONSTRAINT DEF_Name DEFAULT '' FOR Record_Status
其他推荐答案
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'
更多相关问答
联系站长