MS SQL入门基础:SQL数据库表的修改

　　当表创建好后，可能根据需要要对表的列、约束等属性进行添加、删除或修改，这就需要修改表结构。

　　7.4.1 用Enterprise Manager 修改

　　在Enterprise Manager 中选择要进行改动的表，单击右键，从快捷菜单中选择“DesignTable”选项，则会出现如图7-4 所示的修改表结构对话框。可以在图7-4 所示的对话框中修改列的数据类型、名称等属性或添加、删除列，也可以指定表的主关键字约束。单击工具栏中的图标，出现如图7-5 所示的编辑表和约束的属性的对话框。可以在其中编辑各种约束和一些表的属性。

　　7.4.2 用ALTER TABLE 命令修改

　　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 ]

　　| [ 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 [ ASC | DESC ] [,...n] ) }

　　[ WITH FILLFACTOR = fillfactor]

　　[ON {filegroup | DEFAULT} ] ]

　　| FOREIGN KEY

　　[(column[,...n])]

　　REFERENCES ref_table [(ref_column[,...n])]

　　[NOT FOR REPLICATION]

　　[ ON DELETE { CASCADE | NO ACTION } ]

　　[ ON UPDATE { CASCADE | NO ACTION } ]

　　| CHECK [NOT FOR REPLICATION]

　　(search_conditions)}

　　各参数说明如下：

table

　　指定要修改的表的名称。如果表不在当前数据库中或表不属于当前的用户，就必须指明其所属的数据库名称和所有者名称。 ALTER COLUMN

　　 new_data_type

　　指定新的数据类型名称，其使用标准如下：

　　列的原数据类型应可以转换为新的数据类型；

　　新的数据类型不能为TIMESTAMP；

　　新的数据类型允许列为NULL 值；

　　如果原来的列是IDENTITY 列，则新的数据类型应支持IDENTITY 特性；

　　当前的SET ARITHABORT 设置将被视为处于ON 状态。 precision

　　指定新数据类型的位数。 scale

　　指定新数据类型的小数位数。 NULL | NOT NULL

　　指明列是否允许NULL 值。如果添加列到表中时，指定它为NOT NULL， 则必须指定此列的缺省值。选择此项后，new_data_type [(precision [, scale ])]选项就必须指定，即使precision 和scale 选项均不变，当前的数据类型也需要指出来。 WITH CHECK | WITH NOCHECK

　　指定已经存在于表中的数据是否需要使用新添加的或刚启用的FOREIGN KEY 约束或CHECK 约束来验证。如果不指定，WITH CHECK 作为新添加约束的缺省选项，WITH NOCHECK 作为启用旧约束的缺省选项。 {ADD | DROP} ROWGUIDCOL

　　添加或删除列的ROWGUIDCOL 属性。ROWGUIDCOL 属性只能指定给一个 UNIQUEIDENTIFIER 列。 ADD

　　添加一个或多个列、计算列或表约束的定义。 computed_column_expression

　　计算列的计算表达式。 DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

　　指定要删除的约束或列的名称。处于下列情况的列不能删除；

　　用于复制的列；

　　用于索引的列；

　　用于CHECK FOREIGN KEY UNIQUE 或PRIMARY KEY 约束的列；

　　定义了缺省约束或绑定了一个缺省值对象的列；

　　绑定了规则（Rule）的列。 { CHECK | NOCHECK} CONSTRAINT

　　启用或禁用FOREIGN KEY 或CHECK 约束。 ALL

　　使用NOCHECK 选项禁用所有的约束，或使用CHECK 选项启用所有的约束。 {ENABLE | DISABLE} TRIGGER

　　启用或禁用触发器。 ALL

　　启用或禁用选项针对所有的触发器。 trigger_name

　　指定触发器名称。 其它参数与创建表和约束中所讲的相同。

　　例7-13： 创建一个定货商信息表，然后修改简介列的数据类型。

　　create table order_firm (

　　order_firm_id char (8) primary key,

　　firm_name varchar (50) not null

　　firm_introduce char(50) null

　　) on [primary]

　　alter table order_firm

　　alter column firm_introduce varchar(250) null

　　例7-14： 创建一个定货表再插入一个定货商编号列。

　　create table orders(

　　order_id char(8) ,

　　p_id char(8) foreign key references products(p_id),

　　order_quantity smallint check (order_quantity>=10),

　　constraint pk_order_id primary key (order_id),

　　) on [primary]

　　alter table orders

　　add order_firm_id char(8) null

　　constraint fk_order_firm_id foreign key references order_firm(order_firm_id)

　　例7-15： 更改上例中的检查约束，并删除一个外关键字约束。

　　alter table orders

　　add constraint chk_order_quantity check (order_quantity>=100)

　　drop constraint chk_order_quantity

　　7.4.3 用存储过程Sp_rename 修改表名和列名

　　Sp_rename 存储过程可以修改当前数据库中用户对象的名称，如表、列、索引、存储过程等。其语法如下：

　　sp_rename [@objname =] 'object_name',

　　[@newname =] 'new_name'

　　[, [@objtype =] 'object_type']

　　其中[@objtype =] 'object_type'是要改名的对象的类型，其值可以为‘COLUMN’、‘DATABASE’、‘INDEX’、‘USERDATATYPE’、‘OBJECT’。值‘OBJECT’指代了系统表sysobjects 中的所有对象，如表、视图、存储过程、触发器、规则、约束等。‘OBJECT’值为默认值。

　　例7-16：更改orders 表的列p_id 名称为products_id

　　exec sp_rename 'orders.[p_id]', 'product_id', 'column'

　　运行结果如下：

　　Caution: Changing any part of an object name could break scripts and stored procedures.

　　The column was renamed to 'product_id'.

　　例7-17： 更改orders 表的名称为p_orders。

　　exec sp_rename 'orders', 'p_orders''

　　运行结果如下：

　　Caution: Changing any part of an object name could break scripts and stored procedures.

　　The object was renamed to 'p_orders'.