將具有默認值的列添加到SQL Server中的現有表



Answers

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

包含DEFAULT將使用默認值填充現有行中的列,因此不違反NOT NULL約束。

Question

如何將具有默認值的列添加到SQL Server 2000 / SQL Server 2005中的現有表中?




使用:

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

參考: ALTER TABLE(Transact-SQL) (MSDN)




要使用默認值將列添加到現有數據庫表,我們可以使用:

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

這是兩種將列添加到具有默認值的現有數據庫表的方法。




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



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



如果默認值為空,則:

  1. 在SQL Server中,打開目標表的樹
  2. 右鍵單擊“列”==> New Column
  3. 鍵入列名稱, Select Type ,並選中允許空值複選框
  4. 從菜單欄中,單擊Save

完成!




使用:

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

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



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



在SQL Server 2008-R2中,我進入了設計模式 - 在測試數據庫中 - 使用設計器添加我的兩列,並使用GUI進行設置,然後臭名昭著的右鍵單擊選項“ 生成更改腳本 ” !

Bang up彈出一個小窗口,你猜對了,格式正確的保證工作改變腳本。 點擊輕鬆按鈕。




這也可以在SSMS GUI中完成。 我在下面顯示一個默認日期,但默認值可以是任何,當然。

  1. 將你的表放入設計視圖(右鍵單擊對象瀏覽器 - >設計中的表格)
  2. 在表中添加一列(或者如果它已經存在,點擊你想更新的列)
  3. 在下面的列屬性中,在默認值或綁定字段中輸入(getdate())abc0或任何您需要的 ,如下圖所示:




首先創建一個名為student的表格:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

添加一列:

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

SELECT * 
FROM STUDENT

該表被創建並且列被添加到具有默認值的現有表中。




這有很多答案,但我覺得需要添加這個擴展方法。 這看起來要長很多,但是如果您將一個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 tbl_table ADD int_column int NOT NULL DEFAULT(0)

從這個查詢中,您可以添加一個整數數據類型的列,其默認值為0。




例:

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



注意何時添加的列有NOT NULL約束,但沒有DEFAULT約束(值)。 如果表中有任何行, ALTER TABLE語句在這種情況下將失敗。 解決方案是從新列刪除NOT NULL約束,或為其提供DEFAULT約束。




Related