示例 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. 更改表以添加未验证的约束 下例向表中的现有列上添加约束。该列中存在一个违反约束的值;因此,利用 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 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 为表中的各现有行提供值。如果没有使用 WITH VALUES,那么每一行的新列中都将具有 NULL 值。ALTER TABLE MyTable ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT getdate() WITH VALUESG. 禁用并重新启用一个约束 下例禁用用于限制可接受的薪水数据的约束。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
ALTER COLUMN 成绩 numeric(4,1)
ALTER TABLE
通过更改、添加、除去列和约束,或者通过启用或禁用约束和触发器来更改表的定义。语法
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 )
}
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. 更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值;因此,利用 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
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 为表中的各现有行提供值。如果没有使用 WITH VALUES,那么每一行的新列中都将具有 NULL 值。ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUESG. 禁用并重新启用一个约束
下例禁用用于限制可接受的薪水数据的约束。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