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