Oracle約束操作

　　Oracle約束操作

　　約束是在表中定義的用於維護數據庫完整性的一些規則通過為表中的字段定義約束可以防止將錯誤的數據插入到表中

　　注意

　　如果某個約束只作用於單獨的字段既可以在字段級定義約束也可以在表級定義約束;但如果某個約束將作用於多個字段必須在表級定義約束

　　oracle中的約束通過名稱來進行識別在定義約束時可以通過constraint關鍵字為約束命名如果用戶沒有為約束指定名稱oracle將自動為約束建立默認的名稱

　　主鍵約束(primary key)

　　主鍵約束的特點

　　定義為主鍵約束的字段中不能包含任何重復值並且不能包含null值

　　同個表中只能定義個主鍵約束

　　可以為一個字段定義主鍵約束也可以為多個字段的組合定義主鍵約束

　　oracle會自動為具有主鍵約束的字段建立個唯一索引和個非空約束

　　例如

　　create table person

　　(

　　p_id int primary key 定義該字段為主鍵約束

　　p_name varchar()

　　p_age int

　　constraint p_pk primary key (p_id)

　　);

　　create table person

　　(

　　p_id int constraint p_pk primary key 定義該字段為主鍵約束並指定約束名字

　　p_name varchar()

　　p_age int

　　constraint p_pk primary key (p_id)

　　);

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int

　　constraint p_pk primary key (p_idp_name)定義復合主鍵並指定名字

　　primary key(p_idp_name)

　　);

　　非空約束(not null)

　　非空約束的特點

　　定義了非空約束的字段中不能包含null值

　　只能在字段級定義非空約束

　　在同一個表中可以定義多個非空約束

　　例如

　　create table person

　　(

　　p_id int

　　p_name varchar() not null 定義該列的非空約束

　　p_age int

　　);

　　唯一約束(unique):

　　唯一約束的特點

　　定義了唯一約束的字段中不能包含重復值

　　可以為個字段定義唯一約束也可以為多個字段的組合定義唯一約束因此唯一約束既可以定義在字段級也可以定義在表級

　　oracle會自動為具有唯一約束的字段建立個唯一索引

　　對同一字段可以同時定義非空和唯一約束

　　如果在個字段上僅定義了唯一約束而沒有定義非空約束則該字段可以包含多個null值

　　例如

　　create table person

　　(

　　p_id int

　　p_name varchar() unique定義該列的唯一約束

　　p_age int

　　);

　　create table person

　　(

　　p_id int

　　p_name varchar() constraint p_un unique not null 同時定義唯一和非空約束

　　p_age int

　　);

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int

　　constraint p_un unique(p_name) 指定約束名字

　　);

　　外鍵約束(foreign key)

　　外鍵約束的特點

　　定義為外鍵約束的字段中只能包含相應的其他表中引用字段的值或null值

　　可以為個字段定義外鍵約束也可以為多個字段的組合定義外鍵約束

　　定義了外鍵約束的字段和相應的引用字段可以存在於同個表中稱為自引用

　　對同個字段可以同時定義外鍵和非空約束

　　主表中的被引用列必須有主鍵約束或唯一約束

　　例如

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int

　　w_id int

　　constraint p_fk foreign key (w_id) references works(w_id) 外鍵約束

　　);

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int

　　w_id int constraint p_fk references works 外鍵約束引用works表中的主鍵

　　);

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int

　　w_id int references works 外鍵約束

　　 w_id int constraint w_fk references works(w_id)

　　);

　　在定義外鍵約束時還可以通過on關鍵字來指定引用行為的類型當主表中的一條記錄被刪除時需要通過引用行為來確定如何處理子表中的外鍵列的值

　　刪除子表中所有相關的記錄(delete cascade)

　　將所有相關記錄的外鍵值設置為null(delete set null)

　　檢查約束(check)

　　檢查約束的特點

　　在檢查約束的表達式中必須引用到表中的一個或多個字段並且表達式的計算結果必須是一個布爾值

　　在表達式中不能包含子查詢

　　在表達式中不能包含sysdateuiduseruserenv等sql函數也不能包含rowidrownum等偽列

　　檢查約束可以在字段級和表級定義

　　對同一個字段可以定義多個檢查約束而且對同一個字段可以同時定義檢查約束和非空約束

　　例如

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int check(p_age > ) 檢查約束

　　 constraint p_check check(p_age > ) 檢查約束

　　);

　　create table person

　　(

　　p_id int

　　p_name varchar()

　　p_age int constraint p_check check(p_age > ) 檢查約束

　　 constraint p_check check(p_age > ) 檢查約束

　　);

　　增加約束

　　如果增加UNIQUEPRIMARY KEYFOREIGN KEY 和CKECK 必須使用ALTER TABLE語句的ADD子句;

　　如果增加NOT NULL約束那麼必須使用ALTER TABLE語句的MODIFY子句如

　　ALTER TABLE table_name ADD [CONSTRAINT constraint_name]

　　constraint_type (column)

　　ALTER TABLE table_name MODIFY column

　　[CONSTRAINT constraint_name] NOT NULL;

　　添加外鍵約束

　　alter table emp add foreign key (deptno) references dept(deptno);

　　alter table emp add constraint d_fk foreign key (deptno) references dept(deptno);

　　添加主鍵約束

　　alter table table_name add primary key(column_name);

　　alter table table_name add constraint t_pk primary key(column_name);

　　添加非空約束

　　alter table person modify p_name not null;

　　alter table person modify p_name constraint t_notnull not null;

　　約束延遲(在事務提交時再驗證默認為不延遲)

　　deferrable 延遲驗證

　　not deferrable 不延遲驗證(默認)

　　單獨deferrable的含義就是是否允許約束檢查延後進行單獨設置deferrable為deferrable之後約束檢查延後是不可以直接使用的要配合deferred參數如果該參數是immediate那麼約束還是在DML的時候進行應用如果deferred參數設置為deferred約束就是在事務commit提交的時候應用出現錯誤就連帶回滾rollback整個事務

　　創建約束時可指定開啟延遲

　　create table tt

　　(

　　id int primary key deferrable initially deferred

　　);

　　set constraints 約束名 immediate; 關閉約束延遲

　　set constraints 約束名 deferred; 開啟約束延遲

　　也可以在session級設置

　　alter session set constraints=immediate;

　　alter session set constraints=deferred;

　　重命名約束

　　alter table 表名 rename constraint 原約束名 to 新約束名

　　刪除約束

　　alter table 表名 drop constraint 約束名;

　　alter table 表名 drop constraint 約束名 cascade; &#;刪除主鍵時級聯刪除子表的外鍵約束

　　約束失效

　　alter table 表名 disable constraint 約束名

　　alter table 表名 disable constraint 約束名cascade; 失效主鍵時級聯刪除子表的外鍵約束

　　約束生效

　　alter table 表名 enable constraint 約束名

　　novalidate: 不驗證老數據

　　validate驗證老數據

　　控制現有數據是否應用約束例如

　　alter table t deferrable enable novalidate constraint SYS_C; 約束生效不驗證已有數據是否符合約束條件

　　alter table t enable validate constraint SYS_C; 約束生效同時驗證已有數據是否符合約束條件

　　alter table t disable validate constraint SYS_C;

　　約束失效同時驗證已有數據是否符合約束條件之後不能對該表進行dml操作否則會報ORA錯誤

　　alter table t disable novalidate constraint SYS_C;

　　 約束失效不驗證已有數據是否符合約束條件(默認)

　　注約束生效(enable)後的操作都受約束的限制

　　查詢約束信息

　　視圖名字 描述

　　all_constraints 包含所有的約束基本描述信息包括約束的名字類型狀態延遲性等信息

　　dba_constraints

　　user_constraints

　　all_cons_columns 包含定義課約束的字段信息利用這個視圖可以查看約束定義在哪些字段上

　　dba_cons_columns

　　user_cons_columns

From:http://tw.wingwit.com/Article/program/Oracle/201311/17214.html