constraint約束

pwz1688發表於2014-04-24
AI
一、約束型別五種
not null：specifies that a column cannot null values 指定列值不能為空
unique：designates a column or combination of columns as unique 指定列或列組合，值唯一
primary key：designates a column or combination of columns as the table's primary key 指定列或列組合為表的主鍵
foreign key：designates a column or combination of columns as the foreign key in a referential integrity constraint 指定的列或列組合作為一個參照完整性約束外來鍵
check：specifies a condition that each row of table must satisfy 指定所有列必須滿足的約束條件
二、約束的狀態有四種
如下：
enable validate：對已經存在資料和新資料都校驗。啟用校驗
enable novalidate：舊資料不校驗，新資料校驗。啟用校驗，既往不咎
disable validate：在該欄位上不能做dml操作。記成dv狀態，看就好。
disable novalidate：禁止約束，對所有資料。不校驗，全是否定詞
預設狀態是enable validate
建立約束後，在該表或列上做dml操作時有兩種約束：延遲性約束和非延遲性約束。

--檢視約束資訊
SQL> select constraint_name,constraint_type,table_name,deferrable from dba_constraints where owner='SCOTT';
CONSTRAINT CONSTRAINT TABLE_NAME DEFERRABLE
---------- ---------- ---------- --------------
PK_DEPT P DEPT NOT DEFERRABLE
PK_EMP P EMP NOT DEFERRABLE
FK_DEPTNO R EMP NOT DEFERRABLE
SQL> select constraint_name,table_name,column_name from dba_cons_columns where owner='SCOTT';
CONSTRAINT TABLE_NAME COLUMN_NAM
---------- ---------- ----------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO
FK_DEPTNO EMP DEPTNO
--建立emp1表
SQL> create table emp1 as select * from scott.emp;
Table created.
SQL> select * from emp1;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
      7566 JONES MANAGER 7839 02-APR-81 2975 20
      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7788 SCOTT ANALYST 7566 19-APR-87 3000 20
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
      7876 ADAMS CLERK 7788 23-MAY-87 1100 20
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES CLERK 7698 03-DEC-81 950 30
      7902 FORD ANALYST 7566 03-DEC-81 3000 20
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
--修改empno列資訊，使其有重複值
SQL> update emp1 set empno=7788 where empno=7900;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from emp1;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
      7566 JONES MANAGER 7839 02-APR-81 2975 20
      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7788 SCOTT ANALYST 7566 19-APR-87 3000 20
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
      7876 ADAMS CLERK 7788 23-MAY-87 1100 20
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 JAMES CLERK 7698 03-DEC-81 950 30
      7902 FORD ANALYST 7566 03-DEC-81 3000 20
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
--emp1表建立主鍵empno（預設為enable validate)，報錯
SQL> alter table emp1 add constraint pk_emp1 primary key(empno);
alter table emp1 add constraint pk_emp1 primary key(empno)
                                *
ERROR at line 1:
ORA-02437: cannot validate (SYS.PK_EMP1) - primary key violated
--建立enable novalidate主鍵（也報錯，因為建立索引時失敗）
SQL> alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate;
alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate
                                *
ERROR at line 1:
ORA-02437: cannot validate (SYS.PK_EMP1) - primary key violated
--先給empno新增索引，再建enable novalidate主鍵約束
SQL> create index i_emp1 on emp1(empno) tablespace indx;
Index created.
SQL> alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate;
Table altered.
由此可見，只要為主鍵列建立相應索引後，再建enable novalidate主鍵約束，就算主鍵列有重複值（7788）也能建立成功，下面再次驗證enable novalidate狀態的約束特性
--先插入重複列值（插入失敗）
SQL> insert into emp1(empno) values(7788);
insert into emp1(empno) values(7788)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.PK_EMP1) violated
--再插入非重複列值（成功）
SQL> insert into emp1(empno) values(9999);
1 row created.
SQL> commit;
Commit complete.
以上測試結果足已驗證enable novalidate狀態約束“ 既往不咎”的特性。
--將pk_emp1主鍵約束狀態改為enable validate
SQL> alter table emp1 enable validate constraint pk_emp1;
alter table emp1 enable validate constraint pk_emp1
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.PK_EMP1) - primary key violated
修改失敗是因為完全約束，會對錶中內容進行約束，之前提到過empno中7788值有重複，下面先去重，再改約束狀態
SQL> select * from emp1;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
      7566 JONES MANAGER 7839 02-APR-81 2975 20
      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7788 SCOTT ANALYST 7566 19-APR-87 3000 20
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
      7876 ADAMS CLERK 7788 23-MAY-87 1100 20
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 JAMES CLERK 7698 03-DEC-81 950 30
      7902 FORD ANALYST 7566 03-DEC-81 3000 20
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
      9999
15 rows selected.
SQL> update emp1 set empno=7900 where ename='JAMES';
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table emp1 enable validate constraint pk_emp1;
Table altered.
--設定約束狀態為disable novalidate 完全失效
SQL> alter table emp1 disable novalidate constraint pk_emp1;
Table altered.
--插入重複值（成功）
SQL> insert into emp1(empno) values(7788);
1 row created.
SQL> commit;
Commit complete.
三、去除重複值
方法一：通過系統exceptions表來去重複值
SQL> select * from exceptions;
select * from exceptions
              *
ERROR at line 1:
ORA-00942: table or view does not exist
--執行建立exceptions表的語句
SQL> @?/rdbms/admin/utlexpt1
Table created.
SQL> select * from exceptions;
no rows selected

SQL> alter table emp1 enable validate constraint pk_emp1 exceptions into exceptions;
alter table emp1 enable validate constraint pk_emp1 exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.PK_EMP1) - primary key violated
SQL> col row_id for a30;
SQL> col owner for a10;
SQL> col table_name for a10;
SQL> col constraint for a10;
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME CONSTRAINT
------------------------------ ---------- ---------- ----------
AAACjmAABAAAG56AAP SYS EMP1 PK_EMP1
AAACjmAABAAAG56AAH SYS EMP1 PK_EMP1
由此可根據exceptions中rowid去除表中重複資料，但當重複值很多時，此方法不適用，下面講個實用的去重方法
方法二：先取出重複值中的最大或最小值rowid，然後刪除不是這些最大或最小值的其它所有資料
--檢視錶中所有資訊
SQL> select * from emp1 a where a.rowid not in(select min(rowid) from emp1 b where a.empno=b.empno);
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788
SQL> select * from emp1;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
      7566 JONES MANAGER 7839 02-APR-81 2975 20
      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7788 SCOTT ANALYST 7566 19-APR-87 3000 20
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
      7876 ADAMS CLERK 7788 23-MAY-87 1100 20
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES CLERK 7698 03-DEC-81 950 30
      7902 FORD ANALYST 7566 03-DEC-81 3000 20
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
      9999
      7788
16 rows selected.
--檢視重複值中最小rowid的記錄
SQL> select * from emp1 a where a.rowid in(select min(rowid) from emp1 b where a.empno=b.empno);
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
      7876 ADAMS CLERK 7788 23-MAY-87 1100 20
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7902 FORD ANALYST 7566 03-DEC-81 3000 20
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
      9999
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
      7566 JONES MANAGER 7839 02-APR-81 2975 20
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT ANALYST 7566 19-APR-87 3000 20
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
      7900 JAMES CLERK 7698 03-DEC-81 950 30
15 rows selected.
--刪除重複記錄
SQL> select * from emp1 a where a.rowid not in(select min(rowid) from emp1 b where a.empno=b.empno);
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788
SQL> delete from emp1 a where a.rowid not in(select min(rowid) from emp1 b where a.empno=b.empno);
1 row deleted.
四、延遲性約束
--建立dept1表，併為其建立主鍵約束（deptno），為emp1表建立延遲性外來鍵約束（deptno）
SQL> create table dept1 as select * from scott.dept;
Table created.
SQL> alter table dept1 add constraint pk_dept1 primary key(deptno);
Table altered.
SQL> alter table emp1 add constraint fk_dept1 foreign key(deptno) references dept1(deptno) deferrable;
Table altered.
--檢視emp1和dept表資訊
SQL> select * from emp1;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 800 20
      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
      7566 JONES MANAGER 7839 02-APR-81 2975 20
      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7788 SCOTT ANALYST 7566 19-APR-87 3000 20
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
      7876 ADAMS CLERK 7788 23-MAY-87 1100 20
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES CLERK 7698 03-DEC-81 950 30
      7902 FORD ANALYST 7566 03-DEC-81 3000 20
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
      9999
15 rows selected.
SQL> select * from dept1;
    DEPTNO DNAME LOC
---------- -------------- -------------
        10 ACCOUNTING NEW YORK
        20 RESEARCH DALLAS
        30 SALES CHICAGO
        40 OPERATIONS BOSTON
--修改emp1表deptno資訊，使fk_dept1生效（主要測試延遲性）
SQL> update emp1 set deptno=50 where empno=7788;
update emp1 set deptno=50 where empno=7788
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK_DEPT1) violated - parent key not found
以上測試結果可知,fk_dept1建立時雖設了deferrable但並未生效，非延遲性，下面設定可使其延遲性生效
SQL> set constraint fk_dept1 deferred;
Constraint set.
SQL> update emp1 set deptno=50 where empno=7788;
1 row updated.
修改時，約束失效
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SYS.FK_DEPT1) violated - parent key not found
提交時，約束成功，體現了延遲性特性。
注意：要使建立時，延遲性約束立急生效，建立時應加上deferrable initially deferred，如
 alter table emp1 add constraint fk_dept1 foreign key(deptno) references dept1(deptno) deferrable  initially deferred；
五、刪除約束
刪除有外來鍵指向的約束時需加cascade才可直接刪除
SQL> alter table emp1 drop constraint pk_emp1;
Table altered.
SQL> alter table dept1 drop constraint pk_dept1;
alter table dept1 drop constraint pk_dept1
                                  *
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> alter table dept1 drop constraint pk_dept1 cascade;
Table altered.
建立外來鍵時若加on delete cascade，則刪除父表時自動刪除子表的相關記錄，帶來方便也帶來危險，最好別加。下面例項驗證
--先給emp1表建立一般的外來鍵
SQL> alter table dept1 add constraint pk_dept1 primary key(deptno);
Table altered.
SQL> alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) deferrable initially deferred;
Table altered.
--刪父表記錄，檢視子表相關記錄
SQL> delete from dept1 where deptno=10;
1 row deleted.
SQL> select * from emp1 where deptno=10;
     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK MANAGER 7839 09-JUN-81 2450 10
      7839 KING PRESIDENT 17-NOV-81 5000 10
      7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> rollback;
Rollback complete.
--刪外來鍵，建立on delete cascade外來鍵
SQL> alter table emp1 drop constraint fk_emp1;
Table altered.
SQL> alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete cascade;
Table altered.
--再刪除父表記錄，檢視相關的子表記錄
SQL> delete from dept1 where deptno=10;
1 row deleted.
SQL> select * from emp1 where deptno=10;
no rows selected
SQL> rollback;
Rollback complete.
總結：以上例項驗證可知，建立外來鍵時，若加上on delete cascade則刪父表記錄時，相關子表記錄也全都刪除了，若未加on delete cascade，則刪父表記錄，相關子表記錄不會被刪除。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1148934/，如需轉載，請註明出處，否則將追究法律責任。

相關文章