如何在程序中对数据表结构做修改?

Question

加一字段：alter table 表名 add 列名 类型      -- eg:  alter table 表名 add id int 
删除一字段：alter table 表名 drop column 列名　
改成另一类型:　alter table 表名 alter column 列名 varchar(10)
重命名列:
下例将表 customers 中的列 contact title 重命名为 title。
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

Answer 1

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