一、约束类型五种
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博客 ” ，链接：https://blog.itpub.net/21251711/viewspace-1148934/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
