ALTER TABLE更改、添加、删除列...

来源: 发布时间:星期一, 2008年11月24日 浏览:105次 评论:0

ALTER TABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。
权限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. 禁用并重新启用一个约束下例禁用用于限制半