ORACLE约束的属性
今天处理了一个由于约束插入数据失败的问题，处理时感到有些吃力，三天不练手生啊。在这里回忆一下。
Oracle
数据库Constraint约束的四对属性：Deferrable/not deferrable, Deferred/immediate, enable/disable, validate/novalidate。
www.2cto.com
准备一下环境：
SQL> create table empx as select * from emp;
SQL> create table deptx as select * from dept;
SQL> alter table empx add constraint pk_empx primary key(empno);
SQL> alter table deptx add constraint pk_deptx primary key(deptno);
SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno);
SQL> select * from deptx;
[plain]
DEPTNO DNAME LOC
--------- ------------------------------------------ --------------------------------------- www.2cto.com
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from empx;
[plain]
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
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.
SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';
[plain]
CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED
------------------------ ---------------- ------------------------ ---------------- ---------------- ------------
PK_EMPX ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
FK_DEPTX ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found
这里通过emp和dept表创建了两张表empx和deptx,并在empx表上创建了外键约束fk_deptx。
1.Deferrable,not deferrable(default value)
1)这对属性是定义是否可以延时验证,deferrable是指作验证时机,如果在commit的时check为deferrable,否则为immediate .只有在设置Deferrable才可以设置另一属性