SQL 添加具有初始值（但不是默认值）的新列的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1042260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to add a new column with an initial (but not default) value?
提问by Adrian
I need to add a new column to a MS SQL 2005 database with an initial value. However, I do NOT want to automatically create a default constraint on this column. At the point in time that I add the column the default/initial value is correct, but this can change over time. So, future access to the table MUST specify a value instead of accepting a default.
我需要向 MS SQL 2005 数据库添加一个具有初始值的新列。但是，我不想在此列上自动创建默认约束。在我添加列时，默认/初始值是正确的，但这会随着时间的推移而改变。因此，将来对表的访问必须指定一个值而不是接受默认值。
The best I could come up with is:
我能想到的最好的方法是：
ALTER TABLE tbl ADD col INTEGER NULL
UPDATE tbl SET col = 1
ALTER TABLE tbl ALTER COLUMN col INTEGER NOT NULL
This seems a bit inefficient for largish tables (100,000 to 1,000,000 records).
对于较大的表（100,000 到 1,000,000 条记录），这似乎有点低效。
I have experimented with adding the column with a default and then deleting the default constraint. However, I don't know what the name of the default constraint is and would rather not access sysobjects and put in database specific knowledge.
我已经尝试添加具有默认值的列，然后删除默认约束。但是，我不知道默认约束的名称是什么，宁愿不访问 sysobjects 并放入数据库特定的知识。
Please, there must be a better way.
拜托，一定有更好的方法。
采纳答案by Alex Martelli
I'd ALTER TABLE tbl ADD col INTEGER CONSTRAINT tempname DEFAULT 1first,, and drop the explicitly namedconstraint after (presumably within a transaction).
我ALTER TABLE tbl ADD col INTEGER CONSTRAINT tempname DEFAULT 1首先，然后删除显式命名的约束（大概是在事务中）。
回答by Shannon Severance
To add the column with a default and then delete the default, you can name the default:
要添加具有默认值的列然后删除默认值，您可以命名默认值：
ALTER TABLE tbl ADD col INTEGER NOT NULL CONSTRAINT tbl_temp_default DEFAULT 1
ALTER TABLE tbl drop constraint tbl_temp_default
This filled in the value 1, but leaves the table without a default. Using SQL Server 2008, I ran this and your code, of alter update alterand did not see any noticeable difference on a table of 100,000 small rows. SSMS would not show me the query plans for the alter table statements, so I was not able to compare the resources used between the two methods.
这填充了值 1，但使表没有默认值。使用 SQL Server 2008，我运行了这个和你的代码，alter update alter在 100,000 个小行的表上没有看到任何明显的差异。SSMS 不会向我显示alter table 语句的查询计划，因此我无法比较两种方法之间使用的资源。
回答by trent
Another, maybe more native, way would be:
另一种可能更本地化的方式是：
ALTER TABLE tbl ADD COLUMN col INTEGER NOT NULL DEFAULT 1;
ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT;
I'm not sure how long this function exists, but the PostgreSQL documentationgoes back to version 7.1 and for 7.1 it is already described.
我不确定这个函数存在多久，但是PostgreSQL 文档可以追溯到 7.1 版，并且已经描述了 7.1 版。
回答by Hans Malherbe
You can do it in an insert trigger
您可以在插入触发器中执行此操作
回答by Dave Bauman
If you add a default constraint when creating the table, you won't know what it is called. However, if you add a constraint with ALTER TABLE, you must name the constraint. In this case, you would be able to ALTER TABLE DROP CONSTRAINT (This applies to T-SQL, not sure about other databases.)
如果在创建表时添加默认约束，您将不知道它叫什么。但是，如果使用 ALTER TABLE 添加约束，则必须命名约束。在这种情况下，您将能够 ALTER TABLE DROP CONSTRAINT （这适用于 T-SQL，不确定其他数据库。）
However, this would require you to CREATE TABLE with NULL column, ALTER TABLE to add the constraint, make the column NOT NULL, and finally DROP CONSTRAINT.
但是，这将需要您使用 NULL 列创建 TABLE，ALTER TABLE 添加约束，使列 NOT NULL，最后删除 CONSTRAINT。
I don't believe an insert trigger would work as someone else mentioned, because your rows are already added.
我不相信插入触发器会像其他人提到的那样工作，因为您的行已经添加。
I think the way you describe may, in fact, be the most efficient and elegant solution.
我认为您描述的方式实际上可能是最有效和最优雅的解决方案。
