我可以在 SQL Server 的添加列语句中创建命名默认约束吗?

2021-11-27 00:00:00 constraints code-maintainability sql-server alter-table

在 SQL Server 中，我在表中有一个新列:

In SQL Server, I have a new column on a table:

ALTER TABLE t_tableName 
    ADD newColumn NOT NULL

这失败了，因为我在没有指定默认约束的情况下指定了 NOT NULL.该表不应具有默认约束.

This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint.

为了解决这个问题，我可以使用默认约束创建表，然后将其删除.

To get around this, I could create the table with the default constraint and then remove it.

但是，似乎没有任何方法可以指定默认约束应命名为该语句的一部分，因此我摆脱它的唯一方法是使用存储过程在sys.default_constraints 表.

However, there doesn't appear to be any way to specify that the default constraint should be named as part of this statement, so my only way to get rid of it is to have a stored procedure which looks it up in the sys.default_constraints table.

对于可能经常发生的操作来说，这有点混乱/冗长.有没有人对此有更好的解决方案?

This is a bit messy/verbose for an operation which is likely to happen a lot. Does anyone have any better solutions for this?

推荐答案

这应该有效:

ALTER TABLE t_tableName 
    ADD newColumn VARCHAR(50)
    CONSTRAINT YourContraintName DEFAULT '' NOT NULL

相关文章