ALTER TABLE (Transact-SQL) 示例
A. 添加新列
以下示例将添加一个允许空值的列，而且没有通过 DEFAULT 定义提供的值。 在该新列中，每一行都将有 NULL 值。
CREATE TABLE dbo.doc_exa (column_a INT) ; GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ; GO
EXEC sp_help doc_exa ; GO DROP TABLE dbo.doc_exa ; GO
B. 删除列
以下示例将修改一个表以删除列。
CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ; GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ; GO EXEC sp_help doc_exb ; GO
DROP TABLE dbo.doc_exb ; GO
C. 更改列的数据类型
以下示例将表中列的数据类型由 INT 改为 DECIMAL。
CREATE TABLE dbo.doc_exy (column_a INT ) ; GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ; GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ; GO
DROP TABLE dbo.doc_exy ; GO
D. 添加包含约束的列
以下示例将添加一个包含 UNIQUE 约束的新列。
CREATE TABLE dbo.doc_exc (column_a INT) ; GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ; GO
EXEC sp_help doc_exc ; GO DROP TABLE dbo.doc_exc ; GO
E. 在现有列中添加一个未经验证的 CHECK 约束
以下示例将在表中的现有列中添加一个约束。 该列包含一个违反约束的值。 因此，将使用 WITH NOCHECK 以避免根据现有行验证该约束，从而允许添加该约束。
CREATE TABLE dbo.doc_exd ( column_a INT) ; GO
INSERT INTO dbo.doc_exd VALUES (-1) ; GO
ALTER TABLE dbo.doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1) ; GO
EXEC sp_help doc_exd ; GO DROP TABLE dbo.doc_exd ; GO
F. 在现有列中添加一个 DEFAULT 约束
以下示例将创建一个包含两列的表，在第一列插入一个值，另一列保持为 NULL。 然后在第二列中添加一个 DEFAULT 约束。 验证是否已应用了默认值，另一个值是否已插入第一列以及是否已查询表。
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ; GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ; GO
ALTER TABLE dbo.doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ; GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ; GO
SELECT * FROM dbo.doc_exz ; GO DROP TABLE dbo.doc_exz ; GO
G. 添加多个包含约束的列
以下示例将添加多个包含随新列定义的约束的列。 第一个新列具有 IDENTITY 属性。 表中的每一行在标识列中都有新的增量值。
CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ; GO
ALTER TABLE dbo.doc_exe ADD
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,
-- Add a column that references 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 telephone number format.
column_d VARCHAR(16) NULL CONSTRAINT column_d_chk CHECK (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 dbo.doc_exe ; GO
H. 添加包含默认值的可为空的列
以下示例将添加一个包含 DEFAULT 定义的可为空的列，并使用 WITH VALUES 为表中的各个现有行提供值。 如果没有使用 WITH VALUES，那么每一行的新列中都将具包含 NULL 值。
USE AdventureWorks ; GO
CREATE TABLE dbo.doc_exf ( column_a INT) ; GO
INSERT INTO dbo.doc_exf VALUES (1) ; GO
ALTER TABLE dbo.doc_exf ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES ; GO
DROP TABLE dbo.doc_exf ; GO
I. 禁用和重新启用约束
以下示例将禁用对数据中接受的薪金进行限制的约束。NOCHECK CONSTRAINT 将与 ALTER TABLE 配合使用来禁用该约束，从而允许执行通常会违反该约束的插入操作。CHECK CONSTRAINT 将重新启用该约束。
CREATE TABLE dbo.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 dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);
-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);
-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap; I
NSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);
-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;
J. 删除约束
以下示例将从表中删除 UNIQUE 约束。
CREATE TABLE dbo.doc_exc ( column_a INT CONSTRAINT my_constraint UNIQUE) ; GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ; GO
DROP TABLE dbo.doc_exc ; GO
K. 在表之间切换分区
以下示例创建一个已分区表，并假定在数据库中已经创建了分区方案 myRangePS1。 然后，在 PartitionTable 表的 PARTITION 2 所在的同一文件组中，创建与已分区表结构相同的未分区的表。 最后，将 PartitionTable 表的 PARTITION 2 中的数据切换到 NonPartitionTable 表中。
CREATE TABLE PartitionTable (col1 int, col2 char(10)) ON myRangePS1 (col1) ; GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10)) ON test2fg ; GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ; GO
L. 禁用和重新启用触发器
以下示例将使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以允许执行通常会违反此触发器的插入操作。然后，使用 ENABLE TRIGGER 重新启用触发器。
CREATE TABLE dbo.trig_example (id INT, name VARCHAR(12), salary MONEY) ; GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.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
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ; GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ; GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ; GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ; GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ; GO
M. 创建包含索引选项的 PRIMARY KEY 约束
以下示例将创建 PRIMARY KEY 约束 PK_TransactionHistoryArchive_TransactionID，并设置 FILLFACTOR、ONLINE 和 PAD_INDEX 选项。 生成的聚集索引将与约束同名。
USE AdventureWorks; GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID) WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON) GO
N. 在 ONLINE 模式下删除 PRIMARY KEY 约束
以下示例在 ONLINE 选项设置为 ON 的情况下删除 PRIMARY KEY 约束。
USE AdventureWorks; GO
ALTER TABLE Production.TransactionHistoryArchive DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID WITH (ONLINE = ON); GO
O. 添加和删除 FOREIGN KEY 约束
以下示例将创建 ContactBackup 表，然后更改此表。首先添加引用 Contact 表的 FOREIGN KEY 约束，然后再删除 FOREIGN KEY 约束。
USE AdventureWorks ; GO
CREATE TABLE Person.ContactBackup (ContactID int) ; GO
ALTER TABLE Person.ContactBackup ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID) REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup DROP CONSTRAINT FK_ContactBacup_Contact ; GO
DROP TABLE Person.ContactBackup ;
P. 更改列的大小
以下示例将增大 varchar 列的大小并增加 decimal 列的精度和小数位数。 由于列中包含数据，因此只能增大列的大小。 另外还请注意，col_a 是在唯一索引中定义的。 由于数据类型为 varchar 且索引不是 PRIMARY KEY 约束的结果，因此仍可增大 col_a 的大小。
IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL DROP TABLE dbo.doc_exy; GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2)); GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99); GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy'); GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25); GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4); GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ; GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
