constraint約束
一、約束型別五種
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 約束CONSTRAINTAI
- MySQL——約束(constraint)詳解MySqlAI
- 10、Oracle中的約 束constraintOracleAI
- 【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- 約束
- Javaweb-約束-外來鍵約束JavaWeb
- 03約束
- MySQL 約束MySql
- SQL約束SQL
- 主鍵約束、唯一約束和唯一索引索引
- （10）邏輯綜合新增約束（環境約束）
- 約束介紹
- 差分約束
- 綜合約束
- 完整性約束
- 外來鍵約束
- SQLServer約束介紹SQLServer
- IDELAY約束測試IDE
- MySQL自增約束MySql
- Javaweb-約束案例JavaWeb
- 《學習》6約束
- MySQL 欄位約束MySql
- 泛型的約束理解泛型
- C#泛型約束C#泛型
- Eclipse中引入MyBatis約束EclipseMyBatis
- 約束定理+質數篩
- [20240228]約束novalidate.txt
- 資料庫常用約束資料庫
- Mysql入門【Mysql約束】MySql
- Masonry動畫更新約束動畫
- mysql資料庫約束MySql資料庫
- oracle 註釋和約束Oracle
- 支援向量機(SVM)的約束和無約束優化、理論和實現優化
- Could not resolve com.android.support.constraint:constraint-layout:1.1.3AndroidAI
- 約束和異常處理
- 關於外來鍵約束
- Eclipse中引入spring的約束EclipseSpring
- 差分約束基本講解
- composer 版本約束使用方法