管理數據完整性
一 學習目標
1.實現數據完整性約束 2.管理完整性約束 3.從數據字典中獲取約束信息二 保證數據完整性的方法
1.應用程序代碼控制 2.觸發器控制 3.聲明完整性約束三 約束的類型 (見圖)
1.not null (不能為空) 2.unique (值必須唯一) 3.primary key (not null + unique) 4.foreign key (該表值必須在外鍵表中存在) 5.check (自己加的條件) 6.ref (不熟) 注：Constraints不但可以建立在Table上，也可以建立在View上。
四 約束狀態
1.disable novalidate 既不會約束新增數據也不會驗證已有數據，等同于disable 2.disable validate 約束新增數據但不會驗證已有數據，啟用后禁止DML 3.enable novalidate 約束新增數據但不會驗證已有數據 4.enable validate 約束新增數據并驗證已有數據，等同于enable 下面舉例說明：
SQL> create table dept2 as select * from scott.dept;
Table created
SQL> select * from dept2 order by deptno;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter table dept2 add constraint dept2_u1 unique(deptno);
Table altered
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
INDEX_NAME TABLE_NAME UNIQUENESS
------------------------------ ------------------------------ ----------
DEPT2_U1 DEPT2 UNIQUE
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
DEPT2_U1 ENABLED VALIDATED
SQL> insert into dept2(deptno) values(10);
insert into dept2(deptno) values(10)
ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated
SQL> select * from dept2 order by deptno;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter table dept2 modify constraint dept2_u1 disable novalidate;
Table altered
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
INDEX_NAME TABLE_NAME UNIQUENESS
------------------------------ ------------------------------ ----------
（disable自動移除索引）
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
DEPT2_U1 DISABLED NOT VALIDATED
SQL> insert into dept2(deptno) values(10);
1 row inserted
SQL> select * from dept2 order by deptno;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
10
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter table dept2 modify constraint dept2_u1 enable novalidate;
alter table dept2 modify constraint dept2_u1 enable novalidate
ORA-02299: cannot validate (SYSTEM.DEPT2_U1) - duplicate keys found
（因為enable會去創建唯一性索引，而已有數據deptno存在重復數據10，所以這里不能enable）
SQL> delete from dept2 where deptno=10 and dname is null;
1 row deleted
SQL> alter table dept2 modify constraint dept2_u1 enable novalidate;
Table altered
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
INDEX_NAME TABLE_NAME UNIQUENESS
------------------------------ ------------------------------ ----------
DEPT2_U1 DEPT2 UNIQUE
（enable會自動創建唯一性索引）
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
DEPT2_U1 ENABLED NOT VALIDATED
SQL> insert into dept2(deptno) values(10);
insert into dept2(deptno) values(10)
ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated
SQL> alter table dept2 modify constraint dept2_u1 disable validate;
Table altered
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
INDEX_NAME TABLE_NAME UNIQUENESS
------------------------------ ------------------------------ ----------
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
DEPT2_U1 DISABLED VALIDATED
SQL> insert into dept2(deptno) values(10);
insert into dept2(deptno) values(10)
ORA-25128: No insert/update/delete on table with constraint (SYSTEM.DEPT2_U1) disabled and validated
（disable validate后禁止DML）
SQL> select * from dept2 order by deptno;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter table dept2 modify constraint dept2_u1 enable validate;
Table altered
SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1';
INDEX_NAME TABLE_NAME UNIQUENESS
------------------------------ ------------------------------ ----------
DEPT2_U1 DEPT2 UNIQUE
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
DEPT2_U1 ENABLED VALIDATED
SQL> insert into dept2(deptno) values(10);
insert into dept2(deptno) values(10)
ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated
SQL> select * from dept2 order by deptno;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
六 創建約束
1.建表時定義約束：例：sql約束類型 [CONSTRAINT constraint] {[NOT] NULL |UNIQUE [USING INDEX index_clause] |PRIMARY KEY [USING INDEX index_clause] |REFERENCES [schema.]table [(column)] [ON DELETE CASCADE] |CHECK (condition) } 約束狀態 :== [NOT DEFERRABLE|DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}] ] [DISABLE|ENABLE [VALIDATE|NOVALIDATE]]2.建表后增加約束：
七 使用EXCEPTIONS TABLE
1. 如果異常未創建, 運行腳本 utlexcpt.sql： SQL> @ e:\oracle\rdbms\admin\utlexcpt.sql
表已創建。
SQL> desc exceptions
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
2. 使用異常表
SQL> alter table dept2 modify constraint dept2_u1 disable novalidate;
表已更改。
SQL> insert into dept2 (deptno) values(10);
已創建 1 行。
SQL> alter table dept2
2 enable validate constraint dept2_u1
3 exceptions into exceptions;
alter table dept2
*
第 1 行出現錯誤:
ORA-02299: 無法驗證 (SCOTT.DEPT2_U1) - 找到重復關鍵字
SQL> select rowid,dept2.* from dept2
2 where rowid in (select row_id from exceptions) for update;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAMlpAAEAAAAHkAAA 10 ACCOUNTING NEW YORK
AAAMlpAAEAAAAHlAAB 10
SQL> update dept2
2 set deptno = 50
3 where rowid='AAAMlpAAEAAAAHlAAB';
已更新 1 行。
SQL> commit;
提交完成。
posted on 2012-08-18 23:07
地心引力 閱讀(1334)
評論(0) 編輯 收藏