create table dept (deptno varchar2(10),deptname varchar2(10), constraint pk_deptno primary key (deptno));
create table emp1 (empno varchar2(10),ename varchar2(10),deptno,constraint pk_empno primary key (empno),constraint rf foreign key (deptno) references dept(deptno) )
SQL> select t.owner,
2 t.constraint_name,
3 t.table_name,
4 t.status,
5 t.deferrable,
6 t.deferred,
7 t.validated
8 from dba_constraints t
9 where t.owner = 'A'
10 and t.table_name in ('EMP1','DEPT')
11 order by t.last_change desc;
OWNER CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------------------------------------ ------------------------------ ------------------------------ -------- -------------- --------- -------------
A RF EMP1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
A PK_EMPNO EMP1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
A PK_DEPTNO DEPT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
insert into dept values (10,'it');
insert into dept values (20,'hr');
select * from dept
SQL> select * from dept;
DEPTNO DEPTNAME
---------- ----------
10 it
20 hr
insert into emp1 values(1,'KING',10);
insert into emp1 values(2,'HARI',20);
commit;
SQL> select * from emp1;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 KING 10
2 HARI 20
SQL> drop table emp1;
Table dropped
SQL> flashback table emp1 to before drop;
Done
SQL>
SQL> select t.owner,
2 t.constraint_name,
3 t.table_name,
4 t.status,
5 t.deferrable,
6 t.deferred,
7 t.validated
8 from dba_constraints t
9 where t.owner = 'A'
10 and t.table_name in ('EMP1','DEPT')
11 order by t.last_change desc;
OWNER CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------------------------------------ ------------------------------ ------------------------------ -------- -------------- --------- -------------
A BIN$zvNYd4qnRiaPL75ySONeIA==$0 EMP1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
A PK_DEPTNO DEPT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
SQL> INSERT INTO emp1 VALUES (2,'COTT' ,10);
INSERT INTO emp1 VALUES (2,'COTT' ,10)
ORA-00001: 违反唯一约束条件 (A.BIN$zvNYd4qnRiaPL75ySONeIA==$0)
SQL> INSERT INTO emp1 VALUES (3,'ING', 55);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from emp1;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 KING 10
2 HARI 20
3 ING 55
ocp-047-98
最新推荐文章于 2021-04-08 12:55:21 发布