SQL 基礎之管理方案對象（十七）

使用 ALTER TABLE 語句：

 增加字段

 修改字段

 修改字段預設值

 删除字段

1、增加字段，新字段将成為最後一列：

alter table table

add (column datatype [default expr]

[, column datatype]...);

alter table dept80 add (job_id varchar2(9));

2、修改字段的資料類型、大小和預設值（修改後的預設值隻影響修改後插入表中的資料）

modify (column datatype [default expr]

alter table dept80 modify (last_name varchar2(30));

3、删除字段

alter table table drop (column [, column]..);

alter table dept80 drop column job_id;

SET UNUSED 選項

您可以使用 SET UNUSED 選項标記一個或多個字段為不可用。

您可以使用 DROP UNUSED COLUMNS 删除标記為不可用的字段。

alter table <table_name>

set unused(<column_name> [ ,  <column_name>] );

或

set unused column <column_name>  [ , <column_name>] ;

alter table <table_name> drop unused columns;

管理限制

增加限制文法

 添加或删除限制，不會修改其結構

 啟用和禁用限制

 通過使用 MODIFY 子句添加 NOT NULL 限制

文法：

add [constraint <constraint_name>]

type (<column_name>);

添加限制

Add a FOREIGN KEY constraint to the EMP2 table indicating that a manager must already exist as a valid employee in the EMP2 table.修改表 EMP2 添加 FOREIGN KEY 限制，前提是父節點

alter table emp2 modify employee_id primary key;

alter table emp2

add constraint emp_mgr_fk

foreign key(manager_id)

references emp2(employee_id);

ON DELETE 語句

使用 ON DELETE CASCADE 子句級聯删除：

alter table emp2 add constraint emp_dt_fk

foreign key (department_id)

references departments(department_id) on delete cascade;

使用 ON DELETE SET NULL 當父節點記錄删除時子節點

記錄置空：

references departments(department_id) on delete set null;

延遲限制

延遲限制有以下屬性：

 deferrable 或 not deferrable

 initially deferred 或 initially immediate

alter table dept2 add constraint dept2_id_pk  primary key (department_id)

deferrable initially deferred

 set constraint dept2_id_pk immediate

 alter session set constraints=immediate

INITIALLY DEFERRED  和INITIALLY IMMEDIATE 的差別

INITIALLY DEFERRED

事務結束後驗證

INITIALLY IMMEDIATE

語句執行時立即驗證

create table emp_new_sal (salary number

constraint sal_ck

check (salary > 100)

deferrable initially immediate,

bonus number

constraint bonus_ck

check (bonus > 0 )

deferrable initially deferred );

删除限制

删除表 EMP2 的 emp_mgr_fk 限制：

 alter table emp2 drop constraint emp_mgr_fk;

删除DEPT2表的 PRIMARY KEY 限制并删除相關聯的FOREIGN KEY限制：

 alter table dept2 drop primary key cascade;

禁用限制

使用 ALTER TABLE 語句的 DISABLE 子句禁用完整性限制

應用 CASCADE 選項禁用相關的完整性限制

 alter table emp2 disable constraint emp_dt_fk;

啟用限制

使用 ENABLE 子句激活目前禁用表中定義的完整性限制。

 alter table emp2 enable constraint emp_dt_fk;

如果啟用了唯一鍵或主鍵限制，會自動建立一個唯一索引

級聯限制

CASCADE CONSTRAINTS 子句在 DROP COLUMN 子句中使用。

删除字段 CASCADE CONSTRAINTS 指定子句，同時也将删除所有定義的多列限制。

删除列上定義的 PRIMARY 和 UNIQUE 限制時指定CASCADE CONSTRAINTS 子句，則将删除所有引用它的完整性限制

示例：

alter table emp2 drop column employee_id cascade constraints;

alter table test1 drop (col1_pk, col2_fk, col1) cascade constraints;

Cascade 總結

 删除使用者的時候級聯删除對象

drop user test cascade;

 删表時級聯删除限制

drop table test cascade constraints;

 删字段的時候，級聯删除限制(pk,fk,.c)

alter table test drop column (id) cascade constraints;

alter table test disable constraint pk_test cascade;

create table child (c1 number primary key,c2 number references parent(c1) on delete cascade);

重命名表的列和限制

1、使用 RENAME COLUMN子句的ALTER TABLE語句來重新命名表列。

 alter table marketing rename column team_id to id;

2、使用RENAME CONSTRAINT子句的ALTER TABLE語句來重命名表中任意已存在的限制。

 alter table marketing rename constraint mktg_pk to new_mktg_pk;

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1910400，如需轉載請自行聯系原作者