用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 屬性隻能指定給一個
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'.
。