| |
通過更改、添加、除去列和約束，或者通過啟用或禁用約束和觸發器來更改表的定義。
語法
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
參數
table
是要更改的表的名稱。如果表不在當前數據庫中或者不屬于當前用戶所擁有，可以顯式指定數據庫和所有者。
ALTER COLUMN
指定要更改給定列。如果兼容級別是 65 或小于 65，將不允許使用 ALTER COLUMN。
要更改的列不能是：
數據類型為 text、image、ntext 或 timestamp 的列。
表的 ROWGUIDCOL 列。
計算列或用于計算列中的列。
被復制列。
用在索引中的列，除非該列數據類型是 varchar、nvarchar 或 varbinary，數據類型沒有更改，而且新列大小等于或者大于舊列大小。
用在由 CREATE STATISTICS 語句創建的統計中的列。首先用 DROP STATISTICS 語句刪除統計。由查詢優化器自動生成的統計會由 ALTER COLUMN 自動除去。
用在 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 約束中的列。
用在 CHECK 或 UNIQUE 約束中的列，除非用在 CHECK 或 UNIQUE 約束中的可變長度列的長度允許更改。
有相關聯的默認值的列，除非在不更改數據類型的情況下允許更改列的長度、精度或小數位數。
有些數據類型的更改可能導致數據的更改。例如，將數據類型為 nchar 或 nvarchar 的列更改為 char 或 varchar 類型，將導致擴展字符的轉換。降低列的精度和小數位數可能導致數據截斷。
column_name
是要更改、添加或除去的列的名稱。對于新列，如果數據類型為 timestamp，column_name 可以省略。對于 timestamp 數據類型的列，如果未指定 column_name，將使用名稱 timestamp。
new_data_type
是要更改的列的新數據類型。要更改的列的 new_data_type 應符合下列準則：
原來的數據類型必須可以隱式轉換為新數據類型。
new_data_type 類型不能為 timestamp。
對 ALTER COLUMN，ANSI 空默認值始終打開；如果沒有指定，列將可為空。
對 ALTER COLUMN，ANSI 填充始終打開。
如果要更改的列是標識列，new_data_type 必須是支持標識屬性的數據類型。
將忽略 SET ARITHABORT 的當前設置。ALTER TABLE 語句的行為如同 ARITHABORT 選項為 ON 時一樣。
precision
是指定數據類型的精度。
scale
是指定數據類型的小數位數。
COLLATE < collation_name >
為更改列指定新的排序規則。排序規則名稱既可以是 Windows 排序規則名稱，也可以是 SQL 排序規則名稱。
COLLATE 子句只能用于更改數據類型為 char、varchar、text、nchar、nvarchar 和 ntext 的列的排序規則。如果未指定，則此列采用數據庫的默認排序規則。
若滿足下列條件，則 ALTER COLUMN 不能更改排序規則：
檢查約束、外鍵約束或計算列引用了更改列。
在此列上創建了索引、統計或全文索引。更改列的排序規則時，該列上自動創建的統計將除去。
SCHEMABOUND 視圖或函數引用了此列。
NULL | NOT NULL
指定該列是否可接受空值。不允許空值的列只有在指定了默認值的情況下，才能用 ALTER TABLE 語句向表中添加。添加到表中的新列要么允許空值，要么必須指定默認值。
如果新列允許空值，而且沒有指定默認值，那么新列在表中每一行都包含空值。如果新列允許空值并且指定了新列的默認值，那么可以使用 WITH VALUES 選項在表中所有現有行的新列中存儲默認值。
如果新列不允許空值，那么新列必須具有 DEFAULT 定義，而且新列的所有現有行中將自動裝載該默認值。
可在 ALTER COLUMN 語句中指定 NULL 以使 NOT NULL 列允許空值，但 PRIMARY KEY 約束中的列除外。只有列中不包含空值時，ALTER COLUMN 中才可指定 NOT NULL。必須將空值更新為非空值后，才允許執行 ALTER COLUMN NOT NULL 語句，比如：
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
如果 ALTER COLUMN 中指定了 NULL 或 NOT NULL，那么必須同時指定 new_data_type [(precision [, scale ])]。如果不更改數據類型、精度和小數位數，請指定列的這些值的當前值。
[ {ADD | DROP} ROWGUIDCOL ]
指定在指定列上添加或除去 ROWGUIDCOL 屬性。ROWGUIDCOL 是一個關鍵字，表示列是行全局唯一標識符列。對于每個表只能指派一個 uniqueidentifier 列作為 ROWGUIDCOL 列。ROWGUIDCOL 屬性只能指派給 uniqueidentifier 列。
ROWGUIDCOL 屬性并不強制列中所存儲值的唯一性。該屬性也不會為插入到表中的新行自動生成值。若要為每列生成唯一值，那么或者在 INSERT 語句中使用 NEWID 函數，或者將 NEWID 函數指定為該列的默認值。
ADD
指定要添加一個或多個列定義、計算列定義或者表約束。
computed_column_expression
是一個定義計算列的值的表達式。計算列是并不物理地存儲在表中的虛擬列，該列用表達式計算得出，該表達式使用同一表中的其它列。例如，計算列的定義可以是：cost AS price * qty。表達式可以是非計算列的列名、常量、函數、變量，也可以是用一個或多個運算符連接的上述元素的任意組合。表達式不能為子查詢。
計算列可用于選擇列表、WHERE 子句、ORDER BY 字句或其它任何可以使用常規表達式的位置，但下列情況除外：
計算列不能用作 DEFAULT 或 FOREIGN KEY 約束定義，也不能與 NOT NULL 約束定義一起使用。但是，如果計算列由具有確定性的表達式定義，并且索引列中允許計算結果的數據類型，則可將該列用作索引中的鍵列，或用作 PRIMARY KEY 或 UNIQUE 約束的一部分。
例如，如果表中有整數列 a 和 b，那么計算列 a+b 上可建立索引，而計算列 a+DATEPART(dd, GETDATE()) 上則不能，因為該值將在后續調用時更改。
計算列不能作為 INSERT 或 UPDATE 語句的目標。
說明 由于表中計算列所用列中的各行可能有不同的值，所以計算列的每一行可能有不同的值。
n
是表示前面的項可重復 n 次的占位符。
WITH CHECK | WITH NOCHECK
指定表中的數據是否用新添加的或重新啟用的 FOREIGN KEY 或 CHECK 約束進行驗證。如果沒有指定，對于新約束，假定為 WITH CHECK，對于重新啟用的約束，假定為 WITH NOCHECK。
WITH CHECK 和 WITH NOCHECK 子句不能用于 PRIMARY KEY 和 UNIQUE 約束。
如果不想用新 CHECK 或 FOREIGN KEY 約束對現有數據進行驗證，請用 WITH NOCHECK，除了個別情況，不建議這樣使用。新約束將在以后的所有更新中生效。任何在添加約束時由 WITH NOCHECK 抑制的約束違規都可能導致將來的更新失敗，如果這些更新操作要更新的行中包含不符合約束條件的數據。
查詢優化器不考慮用 WITH NOCHECK 定義的約束。將忽略這些約束，直到使用 ALTER TABLE table CHECK CONSTRAINT ALL語句重新啟用這些約束為止。
DROP { [CONSTRAINT] constraint_name | COLUMN column_name }
指定從表中刪除 constraint_name 或者 column_name。如果兼容級別小于或等于 65，將不允許 DROP COLUMN。可以列出多個列或約束。下面的列不能除去：
被復制列。
用在索引中的列。
用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 約束中的列。
有相關聯的默認值（由 DEFAULT 關鍵字定義）的列，或綁定到默認對象的列。
綁定到規則的列。
{ CHECK | NOCHECK} CONSTRAINT
指定啟用或禁用 constraint_name。如果禁用，將來插入或更新該列時將不用該約束條件進行驗證。此選項只能與 FOREIGN KEY 和 CHECK 約束一起使用。
ALL
指定使用 NOCHECK 選項禁用所有約束，或者使用 CHECK 選項啟用所有約束。
{ENABLE | DISABLE} TRIGGER
指定啟用或禁用 trigger_name。當一個觸發器被禁用時，它對表的定義依然存在；然而，當在表上執行 INSERT、UPDATE 或 DELETE 語句時，觸發器中的操作將不執行，除非重新啟用該觸發器。
ALL
指定啟用或禁用表中所有的觸發器。
trigger_name
指定要啟用或禁用的觸發器名稱。
column_name data_type
新列的數據類型。data_type 可以是任何 Microsoft? SQL Server? 數據類型或用戶定義數據類型。
DEFAULT
是指定列默認值的關鍵字。DEFAULT 定義可用于為表中現有行的新列提供值。DEFAULT 定義不能添加到具有 timestamp 數據類型、IDENTITY 屬性、現有 DEFAULT 定義或綁定默認值的列。如果列已有默認值，必須除去舊默認值后才能添加新默認值。為同 SQL Server 先前版本保持兼容性，向 DEFAULT 賦予約束名是可能的。
IDENTITY
指定新列是標識列。在表中添加新行時，SQL Server 為列提供一個唯一的增量值。標識列通常與 PRIMARY KEY 約束一起用作表的唯一行標識符。IDENTITY 屬性可賦予 tinyint、smallint、int、bigint、decimal(p,0) 或者 numeric(p,0) 列。對于每個表只能創建一個標識列。DEFAULT 關鍵字和綁定默認值不能用于標識列。要么種子和增量都同時指定，要么都不指定。如果二者都未指定，則取默認值 (1,1)。
Seed
是用于表中所裝載的第一行的值。
Increment
是添加到前一行的標識值的增量值。
NOT FOR REPLICATION
指定當復制登錄（如 sqlrepl）向表中插入數據時，不強制 IDENTITY 屬性。也可對約束指定 NOT FOR REPLICATION。當復制登錄向表中插入數據時，不檢查約束條件。
CONSTRAINT
指定 PRIMARY KEY、UNIQUE、FOREIGN KEY 或 CHECK 約束的開始，或者指定 DEFAULT 定義的開始。
constrain_name
是新約束。約束的名稱必須符合標識符規則，但其名稱的首字符不能為 #。如果沒有提供 constraint_name，約束使用系統生成的名稱。
PRIMARY KEY
是通過唯一索引對給定的一列或多列強制實體完整性的約束。對每個表只能創建一個 PRIMARY KEY 約束。
UNIQUE
是通過唯一索引為給定的一列或多列提供實體完整性的約束。
CLUSTERED | NONCLUSTERED
指定為 PRIMARY KEY 或 UNIQUE 約束創建聚集或非聚集索引。PRIMARY KEY 約束默認為 CLUSTERED；UNIQUE 約束默認為 NONCLUSTERED。
如果表中已存在聚集約束或索引，那么在 ALTER TABLE 中就不能指定 CLUSTERED。如果表中已存在聚集約束或索引，PRIMARY KEY 約束默認為 NONCLUSTERED。
WITH FILLFACTOR = fillfactor
指定 SQL Server 存儲索引數據時每個索引頁的充滿程度。用戶指定的 fillfactor 取值范圍從 1 到 100。如果沒有指定，那么默認值為 0。創建索引時，fillfactor 值越低，不必分配新空間即可添加的新索引條目的可用空間就越多。
ON {filegroup | DEFAULT}
指定為約束創建的索引的存儲位置。如果指定了 filegroup，索引將在該文件組內創建。如果指定了 DEFAULT，索引將在默認文件組內創建。如果未指定 ON，索引將在表所在的文件組內創建。當為 PRIMARY KEY 或 UNIQUE 約束添加聚集索引時，如果指定了 ON，那么創建聚集索引時整個表都將移到指定的文件組中。
在這里，DEFAULT 不是一個關鍵字。DEFAULT 是默認文件組的標識符，必須用符號界定，如 ON "DEFAULT" 或 ON [DEFAULT]。
FOREIGN KEY...REFERENCES
是為列中數據提供引用完整性的約束。FOREIGN KEY 約束要求列中的每個值在被引用表的指定列中都存在。
ref_table
是 FOREIGN KEY 約束所引用的表。
ref_column
是新 FOREIGN KEY 約束所引用的一列或多列（置于括號中）。
ON DELETE {CASCADE | NO ACTION}
指定當表中被更改的行具有引用關系，并且該行所引用的行從父表中刪除時，要對被更改行采取的操作。默認設置為 NO ACTION。
如果指定 CASCADE，則從父表中刪除被引用行時，也將從引用表中刪除引用行。如果指定 NO ACTION，SQL Server 將產生一個錯誤并回滾父表中的行刪除操作。
如果表中已存在 ON DELETE 的 INSTEAD OF 觸發器，那么就不能定義 ON DELETE 的CASCADE 操作。
例如，在 Northwind 數據庫中，Orders 表和 Customers 表之間有引用關系。Orders.CustomerID 外鍵引用 Customers.CustomerID 主鍵。
如果對 Customers 表的某行執行 DELETE 語句，并且為 Orders.CustomerID 指定 ON DELETE CASCADE 操作，則 SQL Server 將在 Orders 表中檢查是否有與被刪除的行相關的一行或多行。如果存在相關行，那么 Orders 表中的相關行將隨 Customers 表中的被引用行一同刪除。
反之，如果指定 NO ACTION，若在 Orders 表中至少有一行引用 Customers 表中要刪除的行，則 SQL Server 將產生一個錯誤并回滾 Customers 表中的刪除操作。
ON UPDATE {CASCADE | NO ACTION}
指定當表中被更改的行具有引用關系，并且該行所引用的行在父表中更新時，要對被更改行采取的操作。默認設置為 NO ACTION。
如果指定 CASCADE，則在父表中更新被引用行時，也將在引用表中更新引用行。如果指定 NO ACTION，SQL Server 將產生一個錯誤并回滾父表中的行更新操作。
如果表中已存在 ON DELETE 的 INSTEAD OF 觸發器，那么就不能定義 ON DELETE 的CASCADE 操作。
例如，在 Northwind 數據庫中，Orders 表和 Customers 表之間有引用關系。Orders.CustomerID 外鍵引用 Customers.CustomerID 主鍵。
如果對 Customers 表的某行執行 UPDATE 語句，并且為 Orders.CustomerID 指定 ON UPDATE CASCADE 操作，則 SQL Server 將在 Orders 表中檢查是否有與被更新行相關的一行或多行。如果存在相關行，那么 Orders 表中的相關行將隨 Customers 表中的被引用行一同更新。
反之，如果指定了 NO ACTION，若在 Orders 表中至少存在一行引用 Customers 表中要更新的行，那么 SQL Server 將引發一個錯誤并回滾 Customers 表中的更新操作。
[ASC | DESC]
指定加入到表約束中的一列或多列的排序次序。默認設置為 ASC。
WITH VALUES
指定在添加到現有行的新列中存儲 DEFAULT constant_expression 中所給定的值。只有在 ADD 列子句中指定了 DEFAULT 的情況下，才能使用 WITH VALUES。如果要添加的列允許空值且指定了 WITH VALUES，那么將在現有行的新列中存儲默認值。如果沒有指定 WITH VALUES 且列允許空值，那么將在現有行的新列中存儲 NULL 值。如果新列不允許空值，那么不論是否指定 WITH VALUES，都將在現有行的新列中存儲默認值。
column[,...n]
是新約束所用的一列或多列（置于括號中）。
constant_expression
是用作列的默認值的字面值、NULL 或者系統函數。
FOR column
指定與表級 DEFAULT 定義相關聯的列。
CHECK
是通過限制可輸入到一列或多列中的可能值強制域完整性的約束。
logical_expression
是用于 CHECK 約束的返回 TRUE 或 FALSE 的邏輯表達式。用于 CHECK 約束的 Logical_expression 不能引用其它表，但可引用同一表中同一行的其它列。
注釋
若要添加新數據行，請使用 INSERT 語句。若要刪除數據行，請使用 DELETE 或 TRUNCATE TABLE 語句。若要更改現有行中的值，請使用 UPDATE 語句。
ALTER TABLE 語句指定的更改將立即實現。如果這些更改需要修改表中的行，ALTER TABLE 將更新這些行。ALTER TABLE 將獲取表上的架構修改鎖，以確保在更改期間其它連接不能引用該表（甚至不能引用其元數據）。對表進行的更改將記錄于日志中，并且可以完全恢復。影響非常大的表中所有行的更改，比如除去一列或者用默認值添加 NOT NULL 列，可能需要較長時間才能完成，并會生成大量日志記錄。如同影響大量行的 INSERT、UPDATE 或者 DELETE 語句一樣，這一類 ALTER TABLE 語句也應小心使用。
如果過程高速緩存中存在引用該表的執行計劃，ALTER TABLE 會將這些執行計劃標記為下次執行時重新編譯。
如果 ALTER TABLE 語句指定更改其它表所引用的列值，那么根據引用表中 ON UPDATE 或者 ON DELETE 所指定的操作，將發生以下兩個事件之一。
如果在引用表中沒有指定值或指定了 NO ACTION（默認值），那么 ALTER TABLE 語句導致的更改父表中被引用列的操作將回滾，并且 SQL Server 將引發一個錯誤。
如果在引用表中指定了 CASCADE，那么由 ALTER TABLE 語句導致的對父表的更改將應用于父表及其相關表。
添加 sql_variant 列的 ALTER TABLE 語句會生成下列警告：
The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
因為 sql_variant 的最大長度為 8016 個字節，所以產生該警告。當某 sql_variant 列所含值接近最大長度時，即會超過行長度的最大字節限制。
ALTER TABLE 語句對具有架構綁定視圖的表執行時，所受限制與當前在更改具有簡單索引的表時所受的限制相同。添加列是允許的。但是，不允許刪除或更改參與架構綁定視圖的表中的列。如果 ALTER TABLE 語句要求更改用在架構綁定視圖中的列，更改操作將失敗，并且 SQL Server 將引發一條錯誤信息。有關 SCHEMABINDING 和索引視圖的更多信息，請參見 CREATE VIEW。
創建引用表的架構綁定視圖不會影響在基表上添加或刪除觸發器。
當除去約束時，作為約束的一部分而創建的索引也將除去。而通過 CREATE INDEX 創建的索引必須使用 DROP INDEX 語句來除去。DBCC DBREINDEX 語句可用來重建約束定義的索引部分；而不必使用 ALTER TABLE 先除去再重新添加約束。
必須刪除所有基于列的索引和約束后，才能刪除列。
添加約束時，所有現有數據都要進行約束違規驗證。如果發生違規，ALTER TABLE 語句將失敗并返回一個錯誤。
當在現有列上添加新 PRIMARY KEY 或 UNIQUE 約束時，該列中的數據必須唯一。如果存在重復值，ALTER TABLE 語句將失敗。當添加 PRIMARY KEY 或 UNIQUE 約束時，WITH NOCHECK 選項不起作用。
每個 PRIMARY KEY 和 UNIQUE 約束都將生成一個索引。UNIQUE 和 PRIMARY KEY 約束的數目不能導致表上非聚集索引的數目大于 249，聚集索引的數目大于 1。
如果要添加的列的數據類型為 uniqueidentifier，那么該列可以使用 NEWID() 函數作為默認值，以向表中現有行的新列提供唯一標識符值。
SQL Server 在列定義中并不強制以特定的順序指定 DEFAULT、IDENTITY、ROWGUIDCOL 或列約束。
ALTER TABLE 的 ALTER COLUMN 子句并不會在列上綁定或取消綁定任何規則。必須分別使用 sp_bindrule 或 sp_unbindrule 來綁定或取消綁定規則。
可將規則綁定到用戶定義數據類型。然后 CREATE TABLE 將自動在以該用戶定義數據類型定義的列上綁定該規則。當用 ALTER COLUMN 更改列數據類型時，并不會取消綁定這些規則。原用戶定義數據類型上的規則仍然綁定在該列上。在 ALTER COLUMN 更改了列的數據類型之后，隨后執行的任何從該用戶定義數據類型上取消綁定規則的 sp_unbindrule 都不會導致從更改了數據類型的列上取消綁定該規則。如果 ALTER COLUMN 將列的數據類型更改為綁定了規則的用戶定義數據類型，那么綁定到新數據類型的規則不會綁定到該列。
權限
ALTER TABLE 權限默認授予表的所有者、sysadmin 固定服務器角色成員、db_owner 和 db_ddladmin 固定數據庫角色成員且不可轉讓。
示例
A. 更改表以添加新列
下例添加一個允許空值的列，而且沒有通過 DEFAULT 定義提供值。各行的新列中的值將為 NULL。
CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO
B. 更改表以除去列
下例修改表以刪除一列。
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
C. 更改表以添加具有約束的列
下例向表中添加具有 UNIQUE 約束的新列。
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO
D. 更改表以添加未驗證的約束
下例向表中的現有列上添加約束。該列中存在一個違反約束的值；因此，利用 WITH NOCHECK 來防止對現有行驗證約束，從而允許該約束的添加。
CREATE TABLE doc_exd ( column_a INT)
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO
E. 更改表以添加多個帶有約束的列
下例向表中添加多個帶有約束的新列。第一個新列具有 IDENTITY 屬性；表中每一行的標識列都將具有遞增的新值。
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)
GO
ALTER TABLE doc_exe ADD
/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,
/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
/* Add a nonnull column with a default. */
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO
EXEC sp_help doc_exe
GO
DROP TABLE doc_exe
GO
F. 添加具有默認值的可為空的列
下例添加可為空的、具有 DEFAULT 定義的列，并使用 WITH VALUES 為表中的各現有行提供值。如果沒有使用 WITH VALUES，那么每一行的新列中都將具有 NULL 值。
ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES
G. 禁用并重新啟用一個約束
下例禁用用于限制可接受的薪水數據的約束。WITH NOCHECK CONSTRAINT 與 ALTER TABLE 一起使用，以禁用該約束并使正常情況下會引起約束違規的插入操作得以執行。WITH CHECK CONSTRAINT 重新啟用該約束。
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
H. 禁用并重新啟用觸發器
下例使用 ALTER TABLE 的 DISABLE TRIGGER 選項來禁用觸發器，以使正常情況下會違反觸發器條件的插入操作得以執行。然后下例使用 ENABLE TRIGGER 重新啟用觸發器。
CREATE TABLE trig_example
(id INT,
name VARCHAR(10),
salary MONEY)
go
-- Create the trigger.
CREATE TRIGGER trig1 ON trig_example FOR INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000"
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1,"Pat Smith",100001)
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Attempt an insert that would normally violate the trigger
INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,"Mary Booth",100001)
GO
| |