- 相關軟件
>ALTER TABLE 創(chuàng)建者：webmaster 更新時間：2010-06-26 20:52通過更改、添加、除去列和約束，或者通過啟用或禁用約束和觸發(fā)器來更改表的定義。
語法
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 )
}參數(shù)
table
是要更改的表的名稱。如果表不在當前數(shù)據(jù)庫中或者不屬于當前用戶所擁有，可以顯式指定數(shù)據(jù)庫和所有者。
ALTER COLUMN
指定要更改給定列。如果兼容級別是 65 或小于 65，將不允許使用 ALTER COLUMN。有關更多信息，請參見 sp_dbcmptlevel。
要更改的列不能是：
- 數(shù)據(jù)類型為 text、image、ntext 或 timestamp 的列。
- 表的 ROWGUIDCOL 列。
- 計算列或用于計算列中的列。
- 被復制列。
- 用在索引中的列，除非該列數(shù)據(jù)類型是 varchar、nvarchar 或 varbinary，數(shù)據(jù)類型沒有更改，而且新列大小等于或者大于舊列大小。
- 用在由 CREATE STATISTICS 語句創(chuàng)建的統(tǒng)計中的列。首先用 DROP STATISTICS 語句刪除統(tǒng)計。由查詢優(yōu)化器自動生成的統(tǒng)計會由 ALTER COLUMN 自動除去。
- 用在 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 約束中的列。
- 用在 CHECK 或 UNIQUE 約束中的列，除非用在 CHECK 或 UNIQUE 約束中的可變長度列的長度允許更改。
- 有相關聯(lián)的默認值的列，除非在不更改數(shù)據(jù)類型的情況下允許更改列的長度、精度或小數(shù)位數(shù)。
有些數(shù)據(jù)類型的更改可能導致數(shù)據(jù)的更改。例如，將數(shù)據(jù)類型為 nchar 或 nvarchar 的列更改為 char 或 varchar 類型，將導致擴展字符的轉換。有關更多信息，請參見 CAST 和 CONVERT。降低列的精度和小數(shù)位數(shù)可能導致數(shù)據(jù)截斷。
column_name
是要更改、添加或除去的列的名稱。對于新列，如果數(shù)據(jù)類型為 timestamp，column_name 可以省略。對于 timestamp 數(shù)據(jù)類型的列，如果未指定 column_name，將使用名稱 timestamp。
new_data_type
是要更改的列的新數(shù)據(jù)類型。要更改的列的 new_data_type 應符合下列準則：
- 原來的數(shù)據(jù)類型必須可以隱式轉換為新數(shù)據(jù)類型。
- new_data_type 類型不能為 timestamp。
- 對 ALTER COLUMN，ANSI 空默認值始終打開；如果沒有指定，列將可為空。
- 對 ALTER COLUMN，ANSI 填充始終打開。
- 如果要更改的列是標識列，new_data_type 必須是支持標識屬性的數(shù)據(jù)類型。
- 將忽略 SET ARITHABORT 的當前設置。ALTER TABLE 語句的行為如同 ARITHABORT 選項為 ON 時一樣。
precision
是指定數(shù)據(jù)類型的精度。有關有效精度值的更多信息，請參見精度、小數(shù)位數(shù)和長度。
scale
是指定數(shù)據(jù)類型的小數(shù)位數(shù)。有關有效小數(shù)位數(shù)值的更多信息，請參見精度、小數(shù)位數(shù)和長度。
COLLATE < collation_name >
為更改列指定新的排序規(guī)則。排序規(guī)則名稱既可以是 Windows 排序規(guī)則名稱，也可以是 SQL 排序規(guī)則名稱。有關列表及更多信息，請參見 Windows 排序規(guī)則名稱 和 SQL 排序規(guī)則名稱。
COLLATE 子句只能用于更改數(shù)據(jù)類型為 char、varchar、text、nchar、nvarchar 和 ntext 的列的排序規(guī)則。如果未指定，則此列采用數(shù)據(jù)庫的默認排序規(guī)則。
若滿足下列條件，則 ALTER COLUMN 不能更改排序規(guī)則：
- 檢查約束、外鍵約束或計算列引用了更改列。
- 在此列上創(chuàng)建了索引、統(tǒng)計或全文索引。更改列的排序規(guī)則時，該列上自動創(chuàng)建的統(tǒng)計將除去。
- SCHEMABOUND 視圖或函數(shù)引用了此列。
有關 COLLATE 子句的更多信息，請參見 COLLATE。
NULL | NOT NULL
指定該列是否可接受空值。不允許空值的列只有在指定了默認值的情況下，才能用 ALTER TABLE 語句向表中添加。添加到表中的新列要么允許空值，要么必須指定默認值。
如果新列允許空值，而且沒有指定默認值，那么新列在表中每一行都包含空值。如果新列允許空值并且指定了新列的默認值，那么可以使用 WITH VALUES 選項在表中所有現(xiàn)有行的新列中存儲默認值。
如果新列不允許空值，那么新列必須具有 DEFAULT 定義，而且新列的所有現(xiàn)有行中將自動裝載該默認值。
可在 ALTER COLUMN 語句中指定 NULL 以使 NOT NULL 列允許空值，但 PRIMARY KEY 約束中的列除外。只有列中不包含空值時，ALTER COLUMN 中才可指定 NOT NULL。必須將空值更新為非空值后，才允許執(zhí)行 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 ])]。如果不更改數(shù)據(jù)類型、精度和小數(shù)位數(shù)，請指定列的這些值的當前值。
[ {ADD | DROP} ROWGUIDCOL ]
指定在指定列上添加或除去 ROWGUIDCOL 屬性。ROWGUIDCOL 是一個關鍵字，表示列是行全局唯一標識符列。對于每個表只能指派一個 uniqueidentifier 列作為 ROWGUIDCOL 列。ROWGUIDCOL 屬性只能指派給 uniqueidentifier 列。
ROWGUIDCOL 屬性并不強制列中所存儲值的唯一性。該屬性也不會為插入到表中的新行自動生成值。若要為每列生成唯一值，那么或者在 INSERT 語句中使用 NEWID 函數(shù)，或者將 NEWID 函數(shù)指定為該列的默認值。
ADD
指定要添加一個或多個列定義、計算列定義或者表約束。
computed_column_expression
是一個定義計算列的值的表達式。計算列是并不物理地存儲在表中的虛擬列，該列用表達式計算得出，該表達式使用同一表中的其它列。例如，計算列的定義可以是：cost AS price * qty。表達式可以是非計算列的列名、常量、函數(shù)、變量，也可以是用一個或多個運算符連接的上述元素的任意組合。表達式不能為子查詢。
計算列可用于選擇列表、WHERE 子句、ORDER BY 字句或其它任何可以使用常規(guī)表達式的位置，但下列情況除外：
- 計算列不能用作 DEFAULT 或 FOREIGN KEY 約束定義，也不能與 NOT NULL 約束定義一起使用。但是，如果計算列由具有確定性的表達式定義，并且索引列中允許計算結果的數(shù)據(jù)類型，則可將該列用作索引中的鍵列，或用作 PRIMARY KEY 或 UNIQUE 約束的一部分。
例如，如果表中有整數(shù)列 a 和 b，那么計算列 a+b 上可建立索引，而計算列 a+DATEPART(dd, GETDATE()) 上則不能，因為該值將在后續(xù)調用時更改。
- 計算列不能作為 INSERT 或 UPDATE 語句的目標。
說明 由于表中計算列所用列中的各行可能有不同的值，所以計算列的每一行可能有不同的值。
n
是表示前面的項可重復 n 次的占位符。
WITH CHECK | WITH NOCHECK
指定表中的數(shù)據(jù)是否用新添加的或重新啟用的 FOREIGN KEY 或 CHECK 約束進行驗證。如果沒有指定，對于新約束，假定為 WITH CHECK，對于重新啟用的約束，假定為 WITH NOCHECK。
WITH CHECK 和 WITH NOCHECK 子句不能用于 PRIMARY KEY 和 UNIQUE 約束。
如果不想用新 CHECK 或 FOREIGN KEY 約束對現(xiàn)有數(shù)據(jù)進行驗證，請用 WITH NOCHECK，除了個別情況，不建議這樣使用。新約束將在以后的所有更新中生效。任何在添加約束時由 WITH NOCHECK 抑制的約束違規(guī)都可能導致將來的更新失敗，如果這些更新操作要更新的行中包含不符合約束條件的數(shù)據(jù)。
查詢優(yōu)化器不考慮用 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 約束中的列。
- 有相關聯(lián)的默認值（由 DEFAULT 關鍵字定義）的列，或綁定到默認對象的列。
- 綁定到規(guī)則的列。
{ CHECK | NOCHECK} CONSTRAINT
指定啟用或禁用 constraint_name。如果禁用，將來插入或更新該列時將不用該約束條件進行驗證。此選項只能與 FOREIGN KEY 和 CHECK 約束一起使用。
- ALL
- 指定使用 NOCHECK 選項禁用所有約束，或者使用 CHECK 選項啟用所有約束。
{ENABLE | DISABLE} TRIGGER
指定啟用或禁用 trigger_name。當一個觸發(fā)器被禁用時，它對表的定義依然存在；然而，當在表上執(zhí)行 INSERT、UPDATE 或 DELETE 語句時，觸發(fā)器中的操作將不執(zhí)行，除非重新啟用該觸發(fā)器。
- ALL
- 指定啟用或禁用表中所有的觸發(fā)器。
- trigger_name
- 指定要啟用或禁用的觸發(fā)器名稱。
column_name data_type
新列的數(shù)據(jù)類型。data_type 可以是任何 Microsoft? SQL Server? 數(shù)據(jù)類型或用戶定義數(shù)據(jù)類型。
DEFAULT
是指定列默認值的關鍵字。DEFAULT 定義可用于為表中現(xiàn)有行的新列提供值。DEFAULT 定義不能添加到具有 timestamp 數(shù)據(jù)類型、IDENTITY 屬性、現(xiàn)有 DEFAULT 定義或綁定默認值的列。如果列已有默認值，必須除去舊默認值后才能添加新默認值。為同 SQL Server 先前版本保持兼容性，向 DEFAULT 賦予約束名是可能的。
IDENTITY
指定新列是標識列。在表中添加新行時，SQL Server 為列提供一個唯一的增量值。標識列通常與 PRIMARY KEY 約束一起用作表的唯一行標識符。IDENTITY 屬性可賦予 tinyint、smallint、int、bigint、decimal(p,0) 或者 numeric(p,0) 列。對于每個表只能創(chuàng)建一個標識列。DEFAULT 關鍵字和綁定默認值不能用于標識列。要么種子和增量都同時指定，要么都不指定。如果二者都未指定，則取默認值 (1,1)。
- Seed
- 是用于表中所裝載的第一行的值。
- Increment
- 是添加到前一行的標識值的增量值。
NOT FOR REPLICATION
指定當復制登錄（如 sqlrepl）向表中插入數(shù)據(jù)時，不強制 IDENTITY 屬性。也可對約束指定 NOT FOR REPLICATION。當復制登錄向表中插入數(shù)據(jù)時，不檢查約束條件。
CONSTRAINT
指定 PRIMARY KEY、UNIQUE、FOREIGN KEY 或 CHECK 約束的開始，或者指定 DEFAULT 定義的開始。
constrain_name
是新約束。約束的名稱必須符合標識符規(guī)則，但其名稱的首字符不能為 #。如果沒有提供 constraint_name，約束使用系統(tǒng)生成的名稱。
PRIMARY KEY
是通過唯一索引對給定的一列或多列強制實體完整性的約束。對每個表只能創(chuàng)建一個 PRIMARY KEY 約束。
UNIQUE
是通過唯一索引為給定的一列或多列提供實體完整性的約束。
CLUSTERED | NONCLUSTERED
指定為 PRIMARY KEY 或 UNIQUE 約束創(chuàng)建聚集或非聚集索引。PRIMARY KEY 約束默認為 CLUSTERED；UNIQUE 約束默認為 NONCLUSTERED。
如果表中已存在聚集約束或索引，那么在 ALTER TABLE 中就不能指定 CLUSTERED。如果表中已存在聚集約束或索引，PRIMARY KEY 約束默認為 NONCLUSTERED。
WITH FILLFACTOR = fillfactor
指定 SQL Server 存儲索引數(shù)據(jù)時每個索引頁的充滿程度。用戶指定的 fillfactor 取值范圍從 1 到 100。如果沒有指定，那么默認值為 0。創(chuàng)建索引時，fillfactor 值越低，不必分配新空間即可添加的新索引條目的可用空間就越多。有關詳細信息，請參見 CREATE INDEX。
ON {filegroup | DEFAULT}
指定為約束創(chuàng)建的索引的存儲位置。如果指定了 filegroup，索引將在該文件組內創(chuàng)建。如果指定了 DEFAULT，索引將在默認文件組內創(chuàng)建。如果未指定 ON，索引將在表所在的文件組內創(chuàng)建。當為 PRIMARY KEY 或 UNIQUE 約束添加聚集索引時，如果指定了 ON，那么創(chuàng)建聚集索引時整個表都將移到指定的文件組中。
在這里，DEFAULT 不是一個關鍵字。DEFAULT 是默認文件組的標識符，必須用符號界定，如 ON "DEFAULT" 或 ON [DEFAULT]。
FOREIGN KEY...REFERENCES
是為列中數(shù)據(jù)提供引用完整性的約束。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 觸發(fā)器，那么就不能定義 ON DELETE 的CASCADE 操作。
例如，在 Northwind 數(shù)據(jù)庫中，Orders 表和 Customers 表之間有引用關系。Orders.CustomerID 外鍵引用 Customers.CustomerID 主鍵。
如果對 Customers 表的某行執(zhí)行 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 觸發(fā)器，那么就不能定義 ON DELETE 的CASCADE 操作。
例如，在 Northwind 數(shù)據(jù)庫中，Orders 表和 Customers 表之間有引用關系。Orders.CustomerID 外鍵引用 Customers.CustomerID 主鍵。
如果對 Customers 表的某行執(zhí)行 UPDATE 語句，并且為 Orders.CustomerID 指定 ON UPDATE CASCADE 操作，則 SQL Server 將在 Orders 表中檢查是否有與被更新行相關的一行或多行。如果存在相關行，那么 Orders 表中的相關行將隨 Customers 表中的被引用行一同更新。
反之，如果指定了 NO ACTION，若在 Orders 表中至少存在一行引用 Customers 表中要更新的行，那么 SQL Server 將引發(fā)一個錯誤并回滾 Customers 表中的更新操作。
[ASC | DESC]
指定加入到表約束中的一列或多列的排序次序。默認設置為 ASC。
WITH VALUES
指定在添加到現(xiàn)有行的新列中存儲 DEFAULT constant_expression 中所給定的值。只有在 ADD 列子句中指定了 DEFAULT 的情況下，才能使用 WITH VALUES。如果要添加的列允許空值且指定了 WITH VALUES，那么將在現(xiàn)有行的新列中存儲默認值。如果沒有指定 WITH VALUES 且列允許空值，那么將在現(xiàn)有行的新列中存儲 NULL 值。如果新列不允許空值，那么不論是否指定 WITH VALUES，都將在現(xiàn)有行的新列中存儲默認值。
column[,...n]
是新約束所用的一列或多列（置于括號中）。
constant_expression
是用作列的默認值的字面值、NULL 或者系統(tǒng)函數(shù)。
FOR column
指定與表級 DEFAULT 定義相關聯(lián)的列。
CHECK
是通過限制可輸入到一列或多列中的可能值強制域完整性的約束。
logical_expression
是用于 CHECK 約束的返回 TRUE 或 FALSE 的邏輯表達式。用于 CHECK 約束的 Logical_expression 不能引用其它表，但可引用同一表中同一行的其它列。
注釋
若要添加新數(shù)據(jù)行，請使用 INSERT 語句。若要刪除數(shù)據(jù)行，請使用 DELETE 或 TRUNCATE TABLE 語句。若要更改現(xiàn)有行中的值，請使用 UPDATE 語句。
ALTER TABLE 語句指定的更改將立即實現(xiàn)。如果這些更改需要修改表中的行，ALTER TABLE 將更新這些行。ALTER TABLE 將獲取表上的架構修改鎖，以確保在更改期間其它連接不能引用該表（甚至不能引用其元數(shù)據(jù)）。對表進行的更改將記錄于日志中，并且可以完全恢復。影響非常大的表中所有行的更改，比如除去一列或者用默認值添加 NOT NULL 列，可能需要較長時間才能完成，并會生成大量日志記錄。如同影響大量行的 INSERT、UPDATE 或者 DELETE 語句一樣，這一類 ALTER TABLE 語句也應小心使用。
如果過程高速緩存中存在引用該表的執(zhí)行計劃，ALTER TABLE 會將這些執(zhí)行計劃標記為下次執(zhí)行時重新編譯。
如果 ALTER TABLE 語句指定更改其它表所引用的列值，那么根據(jù)引用表中 ON UPDATE 或者 ON DELETE 所指定的操作，將發(fā)生以下兩個事件之一。
- 如果在引用表中沒有指定值或指定了 NO ACTION（默認值），那么 ALTER TABLE 語句導致的更改父表中被引用列的操作將回滾，并且 SQL Server 將引發(fā)一個錯誤。
- 如果在引用表中指定了 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 個字節(jié)，所以產生該警告。當某 sql_variant 列所含值接近最大長度時，即會超過行長度的最大字節(jié)限制。
ALTER TABLE 語句對具有架構綁定視圖的表執(zhí)行時，所受限制與當前在更改具有簡單索引的表時所受的限制相同。添加列是允許的。但是，不允許刪除或更改參與架構綁定視圖的表中的列。如果 ALTER TABLE 語句要求更改用在架構綁定視圖中的列，更改操作將失敗，并且 SQL Server 將引發(fā)一條錯誤信息。有關 SCHEMABINDING 和索引視圖的更多信息，請參見 CREATE VIEW。
創(chuàng)建引用表的架構綁定視圖不會影響在基表上添加或刪除觸發(fā)器。
當除去約束時，作為約束的一部分而創(chuàng)建的索引也將除去。而通過 CREATE INDEX 創(chuàng)建的索引必須使用 DROP INDEX 語句來除去。DBCC DBREINDEX 語句可用來重建約束定義的索引部分；而不必使用 ALTER TABLE 先除去再重新添加約束。
必須刪除所有基于列的索引和約束后，才能刪除列。
添加約束時，所有現(xiàn)有數(shù)據(jù)都要進行約束違規(guī)驗證。如果發(fā)生違規(guī)，ALTER TABLE 語句將失敗并返回一個錯誤。
當在現(xiàn)有列上添加新 PRIMARY KEY 或 UNIQUE 約束時，該列中的數(shù)據(jù)必須唯一。如果存在重復值，ALTER TABLE 語句將失敗。當添加 PRIMARY KEY 或 UNIQUE 約束時，WITH NOCHECK 選項不起作用。
每個 PRIMARY KEY 和 UNIQUE 約束都將生成一個索引。UNIQUE 和 PRIMARY KEY 約束的數(shù)目不能導致表上非聚集索引的數(shù)目大于 249，聚集索引的數(shù)目大于 1。
如果要添加的列的數(shù)據(jù)類型為 uniqueidentifier，那么該列可以使用 NEWID() 函數(shù)作為默認值，以向表中現(xiàn)有行的新列提供唯一標識符值。
SQL Server 在列定義中并不強制以特定的順序指定 DEFAULT、IDENTITY、ROWGUIDCOL 或列約束。
ALTER TABLE 的 ALTER COLUMN 子句并不會在列上綁定或取消綁定任何規(guī)則。必須分別使用 sp_bindrule 或 sp_unbindrule 來綁定或取消綁定規(guī)則。
可將規(guī)則綁定到用戶定義數(shù)據(jù)類型。然后 CREATE TABLE 將自動在以該用戶定義數(shù)據(jù)類型定義的列上綁定該規(guī)則。當用 ALTER COLUMN 更改列數(shù)據(jù)類型時，并不會取消綁定這些規(guī)則。原用戶定義數(shù)據(jù)類型上的規(guī)則仍然綁定在該列上。在 ALTER COLUMN 更改了列的數(shù)據(jù)類型之后，隨后執(zhí)行的任何從該用戶定義數(shù)據(jù)類型上取消綁定規(guī)則的 sp_unbindrule 都不會導致從更改了數(shù)據(jù)類型的列上取消綁定該規(guī)則。如果 ALTER COLUMN 將列的數(shù)據(jù)類型更改為綁定了規(guī)則的用戶定義數(shù)據(jù)類型，那么綁定到新數(shù)據(jù)類型的規(guī)則不會綁定到該列。
權限
ALTER TABLE 權限默認授予表的所有者、sysadmin 固定服務器角色成員、db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色成員且不可轉讓。
示例
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 GOB. 更改表以除去列
下例修改表以刪除一列。
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 GOC. 更改表以添加具有約束的列
下例向表中添加具有 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 GOD. 更改表以添加未驗證的約束
下例向表中的現(xiàn)有列上添加約束。該列中存在一個違反約束的值；因此，利用 WITH NOCHECK 來防止對現(xiàn)有行驗證約束，從而允許該約束的添加。
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 GOE. 更改表以添加多個帶有約束的列
下例向表中添加多個帶有約束的新列。第一個新列具有 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 GOF. 添加具有默認值的可為空的列
下例添加可為空的、具有 DEFAULT 定義的列，并使用 WITH VALUES 為表中的各現(xiàn)有行提供值。如果沒有使用 WITH VALUES，那么每一行的新列中都將具有 NULL 值。
ALTER TABLE MyTable ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT getdate() WITH VALUESG. 禁用并重新啟用一個約束
下例禁用用于限制可接受的薪水數(shù)據(jù)的約束。WITH NOCHECK CONSTRAINT 與 ALTER TABLE 一起使用，以禁用該約束并使正常情況下會引起約束違規(guī)的插入操作得以執(zhí)行。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. 禁用并重新啟用觸發(fā)器
下例使用 ALTER TABLE 的 DISABLE TRIGGER 選項來禁用觸發(fā)器，以使正常情況下會違反觸發(fā)器條件的插入操作得以執(zhí)行。然后下例使用 ENABLE TRIGGER 重新啟用觸發(fā)器。
相關文章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- 前一則: Transact-SQL 中的新特性和增強的功能
- 后一則: COMMIT WORK
- 數(shù)據(jù)類型為 text、image、ntext 或 timestamp 的列。