|
SQL Server數(shù)據(jù)庫(kù)技術(shù)（46) 當(dāng)表創(chuàng)建好后，可能根據(jù)需要要對(duì)表的列、約束等屬性進(jìn)行添加、刪除或修改，這就需要修改表結(jié)構(gòu)。 7.4.1 用Enterprise Manager 修改 7.4.2 用ALTER TABLE 命令修改 ALTER TABLE 命令可以添加或刪除表的列、約束，也可以禁用或啟用已存在的約束 或觸發(fā)器。其語(yǔ)法如下： ALTER TABLE table { [ALTER COLUMN column_name { new_data_type [ (precision[, scale] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] | {ADD | DROP} ROWGUIDCOL } ] | ADD { [ | column_name AS computed_column_expression }[,...n] | [WITH CHECK | WITH NOCHECK] ADD { }[,...n] | DROP { [CONSTRAINT] constraint_name | COLUMN column }[,...n] | {CHECK | NOCHECK} CONSTRAINT {ALL | constraint_name[,...n]} | {ENABLE | DISABLE} TRIGGER {ALL | trigger_name[,...n]} } [ [ DEFAULT constant_expression ] | [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ] ] [ ROWGUIDCOL ] [ COLLATE < collation_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)} ::= [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)} 各參數(shù)說(shuō)明如下：
例7-13： 創(chuàng)建一個(gè)定貨商信息表，然后修改簡(jiǎn)介列的數(shù)據(jù)類型。 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： 創(chuàng)建一個(gè)定貨表再插入一個(gè)定貨商編號(hào)列。 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： 更改上例中的檢查約束，并刪除一個(gè)外關(guān)鍵字約束。 alter table orders add constraint chk_order_quantity check (order_quantity>=100) drop constraint chk_order_quantity 7.4.3 用存儲(chǔ)過(guò)程Sp_rename 修改表名和列名 Sp_rename 存儲(chǔ)過(guò)程可以修改當(dāng)前數(shù)據(jù)庫(kù)中用戶對(duì)象的名稱，如表、列、索引、存儲(chǔ)過(guò)程等。其語(yǔ)法如下： sp_rename [@objname =] ‘object_name‘, [@newname =] ‘new_name‘ [, [@objtype =] ‘object_type‘] 其中[@objtype =] ‘object_type‘是要改名的對(duì)象的類型，其值可以為‘COLUMN’、‘DATABASE’、‘INDEX’、‘USERDATATYPE’、‘OBJECT’。值‘OBJECT’指代了系統(tǒng)表sysobjects 中的所有對(duì)象，如表、視圖、存儲(chǔ)過(guò)程、觸發(fā)器、規(guī)則、約束等。‘OBJECT’值為默認(rèn)值。 例7-16：更改orders 表的列p_id 名稱為products_id exec sp_rename ‘orders.[p_id]‘, ‘product_id‘, ‘column‘ 運(yùn)行結(jié)果如下： 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‘‘ 運(yùn)行結(jié)果如下： Caution: Changing any part of an object name could break scripts and stored procedures. The object was renamed to ‘p_orders‘. |
|
|
