如何往一个已有表中增加一个不允许为空的列？

Question

大家好，我用下面的语句往一个已有表中增加不允许为空的列：

ALTER TABLE dbo.T_Contract_Cost_Alteration ADD HandlingStaff nvarchar(50) DEFAULT '张三' NOT NULL;	

这行语句执行后，确实增加了“HandlingStaff"这一列，但也带来了一个隐藏的问题：这行语句同时新增了一个默认约束：如下图：



这个隐藏的约束会导致后期如果先用T-SQL语句删除这一列时报错：必须先删除约束，才能再删除这一列。

ALTER TABLE dbo.T_Contract_Cost_Alteration DROP column HandlingStaff;

而且由于这个默认约束的名称是SQL Server自动指定的，无法实现用T-SQL语句自动删除，故采用T-SQL语句更新数据库时要手动操作。故我想知道，有没有更好的办法实现往已有表中增加不为空的列？后续采用T-SQL语句删除这列时也不会报错。

Answer 1

是一定需要用sql语句吗？ 用Navicat这样的工具行不行？

Answer 2

你好，项目开发采用本地数据库，发布时，需要修改服务器上的数据库，故采用SQL语句更新数据库。方便开发的数据库和服务器上正在使用的数据库的一致。服务器上好像不再使用其他工具。

Answer 3

如果表中有数据，则添加不允许NULL值的列必须设置默认值，你可以为默认值约束设置名称的，这样删除的时候，直接知道名称
ALTER TABLE dbo.T_Contract_Cost_Alteration ADD HandlingStaff nvarchar(50) CONSTRAINT DF_T_Contract_Cost_Alteration_HandlingStaff DEFAULT '张三' NOT NULL; 

Answer 4

至于自动分配的默认值约束名称，你可以查系统表获取的，参考下面的，用SQL语句删除的话，针对查询拼一下SQL就成

SELECT
 table_name = TB.name,
 column_name = COL.name,
 default_name = DF.name
FROM sys.tables TB
 INNER JOIN sys.columns COL
  ON COL.object_id = TB.object_id
 INNER JOIN sys.default_constraints  DF
  ON DF.object_id = COL.default_object_id
WHERE TB.name = '你的表名' AND COL.name = '列名'

Answer 5

你的需求可以用red-gate的SQL Compare这个工具完成开发库与正式库的结构同步，red-gate似乎还有可以嵌入程序更新包的数据库更新方案，或者更加适合这个问题，你可以自行了解一下。

Answer 6

你好，你可以先做删除約束再删除Column

alter table T_Contract_Cost_Alteration drop constraint [ConstraintName]
GO
alter table T_Contract_Cost_Alteration drop column HandlingStaff

可是還是有機會會有別的錯誤信息，要根據其他可能的情況。

如果要自動找出所有有關的約束去自動删除，可以用這個

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

---

大家一齊探討、學習和研究，謝謝！
MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD,
MCT, Microsoft Community Star(TW & HK),
Microsoft MVP for VB.NET since 2003
My MSMVP Blog

請記得將對您有幫助的回覆標示為解答以幫助其他尋找解答及參與社群討論的朋友們。
Please remember to clickMark as Answer on the post that helps you. This can be beneficial to other community members reading the thread.

Answer 7

你好，看你的意思是想 在添加不可为空列的同时给原有数据指定一个值

可以先添加一个可为空的列： ALTER TABLE dbo.T_Contract_Cost_Alteration ADD HandlingStaff nvarchar(50)  NULL;

再更新原有数据： UPDATE dbo.T_Contract_Cost_Alteration SET HandlingStaff='张三'

修改列不可为空： ALTER TABLE dbo.T_Contract_Cost_Alteration ALTER COLUMN HandlingStaff nvarchar(50)  NOT NULL

Answer 8

你好，看你的意思是想 在添加不可为空列的同时给原有数据指定一个值

可以先添加一个可为空的列： ALTER TABLE dbo.T_Contract_Cost_Alteration ADD HandlingStaff nvarchar(50)  NULL;

再更新原有数据： UPDATE dbo.T_Contract_Cost_Alteration SET HandlingStaff='张三'

修改列不可为空： ALTER TABLE dbo.T_Contract_Cost_Alteration ALTER COLUMN HandlingStaff nvarchar(50)  NOT NULL

人家已经说了。。。

---

Love SQL