如何将具有默认值的列添加到 SQL Server 2000 / SQL Server 2005 中的现有表?
句法:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
例子:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
笔记:
可选的约束名称:
如果省略 CONSTRAINT D_SomeTable_SomeCol
,则 SQL Server 将自动生成
具有有趣名称的默认约束,例如:DF__SomeTa__SomeC__4FB7FEF6
可选的 With-Values 语句:
仅当您的列为 Nullable 时才需要 WITH VALUES
并且您想要用于现有记录的默认值。
如果您的列是 NOT NULL
,那么它将自动使用默认值
对于所有现有记录,无论您是否指定 WITH VALUES
。
插入如何与默认约束一起使用:
如果您将记录插入 SomeTable
并不指定 SomeCol
' s 值,则默认为 0
。
如果您插入记录 并且 将 SomeCol
的值指定为 NULL
(以及您的列允许空值),
那么默认约束将不被使用,并且 NULL
将作为值插入。
笔记基于以下每个人的出色反馈。特别感谢:@Yatrix、@WalterStabosz、@YahooSerious 和 @StackMan 的评论。
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
包含 DEFAULT 会使用默认值填充现有行中的列,因此不会违反 NOT NULL 约束。
添加可为空的列时,WITH VALUES
将确保将特定的 DEFAULT 值应用于现有行:
ALTER TABLE table
ADD column BIT -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
最基本的版本,只有两行
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
当您添加的列具有 NOT NULL
约束但没有 DEFAULT
约束(值)时请注意。在这种情况下,如果表中有任何行,ALTER TABLE
语句将失败。解决方案是从新列中删除 NOT NULL
约束,或为其提供 DEFAULT
约束。
利用:
-- Add a column with a default DateTime
-- to capture when each record is added.
ALTER TABLE myTableName
ADD RecordAddedDate SMALLDATETIME NULL DEFAULT (GETDATE())
GO
如果你想添加多个列,你可以这样做,例如:
ALTER TABLE YourTable
ADD Column1 INT NOT NULL DEFAULT 0,
Column2 INT NOT NULL DEFAULT 1,
Column3 VARCHAR(50) DEFAULT 'Hello'
GO
要将列添加到具有默认值的现有数据库表,我们可以使用:
ALTER TABLE [dbo.table_name]
ADD [Column_Name] BIT NOT NULL
Default ( 0 )
这是将列添加到具有默认值的现有数据库表的另一种方法。
下面是一个更彻底的 SQL 脚本,用于添加具有默认值的列,包括在添加列之前检查列是否存在,还检查约束并在存在约束时将其删除。该脚本还为约束命名,这样我们就可以有一个很好的命名约定(我喜欢 DF_),如果不是,SQL 会给我们一个具有随机生成数字的名称的约束;所以也很高兴能够命名约束。
-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
IF EXISTS ( SELECT 1
FROM sys.default_constraints
WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
)
BEGIN
------ DROP Contraint
ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
END
-- ----- DROP Column -----------------------------------------------------------------
ALTER TABLE [dbo].table_Emplyee
DROP COLUMN Column_EmployeeName
PRINT 'Column Column_EmployeeName in images table was dropped'
END
--------------------------------------------------------------------------
-- ADD COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
----- ADD Column & Contraint
ALTER TABLE dbo.table_Emplyee
ADD Column_EmployeeName BIT NOT NULL
CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] DEFAULT (0)
PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
END
GO
这是将列添加到具有默认值的现有数据库表的两种方法。
利用:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
参考:ALTER TABLE (Transact-SQL)(MSDN)
您可以通过以下方式使用 T-SQL 执行此操作。
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
您也可以通过右键单击“设计”菜单中的表格来使用 SQL Server Management Studio,将默认值设置为表格。
此外,如果要向数据库中的所有表添加相同的列(如果它不存在),请使用:
USE AdventureWorks;
EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;
在 SQL Server 2008-R2 中,我进入设计模式 - 在测试数据库中 - 并使用设计器添加我的两列并使用 GUI 进行设置,然后臭名昭著的右键单击提供选项“生成更改脚本” !
弹出一个小窗口,您猜对了,正确格式的保证工作更改脚本。点击简单按钮。
或者,您可以添加默认值而无需显式命名约束:
ALTER TABLE [schema].[tablename] ADD DEFAULT ((0)) FOR [columnname]
如果您在创建此约束时对现有默认约束有疑问,则可以通过以下方式删除它们:
alter table [schema].[tablename] drop constraint [constraintname]
这也可以在 SSMS GUI 中完成。我在下面显示了一个默认日期,但默认值当然可以是任何值。
将表格放入设计视图(在对象资源管理器中右键单击表格->设计)向表格中添加列(或单击要更新的列,如果它已经存在)在下面的列属性中,输入(getdate() ) 或 'abc' 或 0 或您想要的默认值或绑定字段中的任何值,如下图所示:
https://i.stack.imgur.com/FQbi1.png
ALTER TABLE ADD ColumnName {Column_Type} Constraint
MSDN 文章 ALTER TABLE (Transact-SQL) 包含所有的 alter table 语法。
例子:
ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
例子:
ALTER TABLE tes
ADD ssd NUMBER DEFAULT '0';
首先创建一个名为 student 的表:
CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)
向其中添加一列:
ALTER TABLE STUDENT
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)
SELECT *
FROM STUDENT
将创建该表并将一列添加到具有默认值的现有表中。
https://i.stack.imgur.com/lMXvg.png
这有很多答案,但我觉得有必要添加这个扩展方法。这似乎要长得多,但如果您将 NOT NULL 字段添加到活动数据库中具有数百万行的表中,则它非常有用。
ALTER TABLE {schemaName}.{tableName}
ADD {columnName} {datatype} NULL
CONSTRAINT {constraintName} DEFAULT {DefaultValue}
UPDATE {schemaName}.{tableName}
SET {columnName} = {DefaultValue}
WHERE {columName} IS NULL
ALTER TABLE {schemaName}.{tableName}
ALTER COLUMN {columnName} {datatype} NOT NULL
这将做的是将列添加为可空字段并使用默认值,将所有字段更新为默认值(或者您可以分配更有意义的值),最后它将将该列更改为 NOT NULL。
这样做的原因是,如果您更新一个大型表并添加一个新的非空字段,它必须写入每一行,因此将在添加列时锁定整个表,然后写入所有值。
此方法将添加可空列,该列本身运行速度更快,然后在设置非空状态之前填充数据。
我发现在一个语句中完成整个操作会将我们更活跃的表之一锁定 4-8 分钟,而且我经常会终止该进程。这种方法每个部分通常只需要几秒钟并且导致最小的锁定。
此外,如果您有一个包含数十亿行的表,则可能值得像这样批量更新:
WHILE 1=1
BEGIN
UPDATE TOP (1000000) {schemaName}.{tableName}
SET {columnName} = {DefaultValue}
WHERE {columName} IS NULL
IF @@ROWCOUNT < 1000000
BREAK;
END
尝试这个
ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
SQL Server + Alter Table + 添加列 + 默认值 uniqueidentifier
ALTER TABLE Product
ADD ReferenceID uniqueidentifier not null
default (cast(cast(0 as binary) as uniqueidentifier))
IF NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TABLENAME' AND COLUMN_NAME = 'COLUMNNAME'
)
BEGIN
ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar(MAX) Not Null default
END
--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
这适用于 SQL Server:
ALTER TABLE TableName
ADD ColumnName (type) -- NULL OR NOT NULL
DEFAULT (default value)
WITH VALUES
例子:
ALTER TABLE Activities
ADD status int NOT NULL DEFAULT (0)
WITH VALUES
如果要添加约束,则:
ALTER TABLE Table_1
ADD row3 int NOT NULL
CONSTRAINT CONSTRAINT_NAME DEFAULT (0)
WITH VALUES
向表中添加新列:
ALTER TABLE [table]
ADD Column1 Datatype
例如,
ALTER TABLE [test]
ADD ID Int
如果用户想让它自动递增,那么:
ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
这可以通过下面的代码来完成。
CREATE TABLE TestTable
(FirstCol INT NOT NULL)
GO
------------------------------
-- Option 1
------------------------------
-- Adding New Column
ALTER TABLE TestTable
ADD SecondCol INT
GO
-- Updating it with Default
UPDATE TestTable
SET SecondCol = 0
GO
-- Alter
ALTER TABLE TestTable
ALTER COLUMN SecondCol INT NOT NULL
GO
尝试使用以下查询:
ALTER TABLE MyTable
ADD MyNewColumn DataType DEFAULT DefaultValue
这将在表格中添加一个新列。
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)
从此查询中,您可以添加默认值为 0 的数据类型整数列。
好吧,我现在对我之前的答案进行了一些修改。我注意到没有提到IF NOT EXISTS
的答案。所以我将提供一个新的解决方案,因为我在更改表格时遇到了一些问题。
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'TaskSheet' AND column_name = 'IsBilledToClient')
BEGIN
ALTER TABLE dbo.TaskSheet ADD
IsBilledToClient bit NOT NULL DEFAULT ((1))
END
GO
这里 TaskSheet
是特定的表名,IsBilledToClient
是您要插入的新列,1
是默认值。这意味着在新列中现有行的值是多少,因此将在那里自动设置一个。但是,您可以根据需要更改列类型,例如我使用的 BIT
,因此我输入默认值 1。
我建议使用上述系统,因为我遇到了一个问题。那么问题是什么?问题是,如果表中确实存在 IsBilledToClient
列,那么如果您只执行下面给出的部分代码,您将在 SQL Server 查询构建器中看到错误。但是如果它不存在,那么第一次执行时不会出错。
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
WITH VALUES
更新现有的可为空行。请参阅 MSDN:“如果添加的列允许空值并且指定了WITH VALUES
,则默认值存储在新列中,并添加到现有行中。”