ocp-047-98

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