提问



如何将具有默认值的列添加到SQL Server 2000/SQL Server 2005中的现有表中?[65] [66]

最佳参考


语法:



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声明:

只有当您的专栏可以为空时才需要WITH VALUES

   并且您想要用于现有记录的默认值。

如果您的列是NOT NULL,那么它将自动使用默认值

   对于所有现有记录,无论您是否指定WITH VALUES


插件如何使用默认约束:

如果您将记录插入SomeTable并执行指定SomeCol的值,则默认为0。< br/>
如果您将记录指定SomeCol的值插入NULL(并且您的列允许空值),

&NBSP; &NBSP;然后默认约束将,并且NULL将作为值插入。



笔记基于以下所有人的好评。

特别感谢:

&NBSP; &NBSP; @Yatrix,@ WalterStabosz,@ YahooSerious和@StackMan的评论。

其它参考1


ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO


包含 DEFAULT 会使用默认值填充现有行中的列,因此不会违反NOT NULL约束。

其它参考2


添加可空列时,WITH VALUES将确保将特定的DEFAULT值应用于现有行:


ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

其它参考3


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

其它参考4


ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'

其它参考5


请注意,当您添加的列具有NOT NULL约束,但没有DEFAULT约束(值)时。如果表中有任何行,则ALTER TABLE语句将失败。解决方案是从新列中删除NOT NULL约束,或为其提供DEFAULT约束。

其它参考6


最基本的版本只有两行


ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

其它参考7


使用:


-- Add a column with a default DateTime  
-- to capture when each record is added.

ALTER TABLE myTableName  
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
GO 

其它参考8


如果要添加多个列,可以这样做,例如:


ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO

其它参考9


使用:


ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}


参考:ALTER TABLE(Transact-SQL)(MSDN)[67]

其它参考10


您可以通过以下方式使用T-SQL执行此操作。


 ALTER TABLE {TABLENAME}
 ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
 CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}


您还可以通过右键单击设计菜单中的表来使用SQL Server Management Studio,将默认值设置为table。[68]


此外,如果要将相同的列(如果它不存在)添加到数据库中的所有表,则使用:


 USE AdventureWorks;
 EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;

其它参考11


在SQL Server 2008-R2中,我进入设计模式 - 在测试数据库中 - 使用设计器添加我的两列并使用GUI进行设置,然后臭名昭着的右键单击给出选项 生成更改脚本 !


Bang up弹出一个小窗口,您猜对了,正确格式化的保证工作更改脚本。按下简单按钮。

其它参考12


或者,您可以添加默认值而无需明确命名约束:


ALTER TABLE [schema].[tablename] ADD  DEFAULT ((0)) FOR [columnname]


如果在创建此约束时遇到现有默认约束的问题,则可以通过以下方式删除它们:


alter table [schema].[tablename] drop constraint [constraintname]

其它参考13


要使用默认值将列添加到现有数据库表,我们可以使用:


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


这两种方法是使用默认值将列添加到现有数据库表。

其它参考14


ALTER TABLE ADD ColumnName {Column_Type} Constraint


MSDN文章 ALTER TABLE(Transact-SQL)具有所有alter table语法。[69]

其它参考15


例:


ALTER TABLE [Employees] ADD Seniority int not null default 0 GO

其它参考16


例:


ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';

其它参考17


这也可以在SSMS GUI中完成。我在下面显示默认日期,但当然默认值可以是任何值。



  1. 将您的表放在设计视图中(右键单击对象中的表
    Explorer的>设计)

  2. 向表中添加一列(或者单击要更新的列,如果
    它已经存在)

  3. 在下面的列属性中,输入(getdate())abc0或默认值或绑定字段中所需的任何值,如下图所示:



[70]

其它参考18


SQL Server +更改表+添加列+默认值uniqueidentifier


ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))

其它参考19


首先创建一个名为student的表:


CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)


向其中添加一列:


ALTER TABLE STUDENT 
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)

SELECT * 
FROM STUDENT


将创建该表,并使用默认值将列添加到现有表中。


[71]

其它参考20


尝试这个


ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO

其它参考21


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

其它参考22


这有很多答案,但我觉得需要添加这个扩展方法。这看起来要长得多,但如果你在一个Activity数据库中有数百万行的表中添加一个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

其它参考23


向表中添加新列:


ALTER TABLE [table]
ADD Column1 Datatype


例如,


ALTER TABLE [test]
ADD ID Int


如果用户想要使其自动递增,则:


ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL

其它参考24


--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

其它参考25


这可以通过以下代码完成。


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

其它参考26


ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)


从此查询中,您可以添加一个数据类型为整数的列,其默认值为0。

其它参考27


好吧,我现在对我以前的答案进行了一些修改。我注意到没有提到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]

其它参考28


如果默认值为Null,则:



  1. 在SQL Server中,打开目标表的树

  2. 右键单击列==> New Column

  3. 键入列名称Select Type,然后选中允许空值复选框

  4. 在菜单栏中,单击Save



完成!

其它参考29


SQL Server + Alter表+添加列+默认值uniqueidentifier ...


ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO