10g recycling bin foreign key behavior

From: Jack <jreid_at_stirlingonline.com>
Date: 13 Oct 2005 12:41:13 -0700
Message-ID: <1129232473.224803.130570@z14g2000cwz.googlegroups.com>

I am puzzled about some 10g recyclebin behavior.

I create two simple tables with a foreign key relationship. The foreign key includes the CASCADE CONSTRAINTS clause.

Why, when I drop the table referenced by the foreign key does the foreign key constraint remain and have a status of ENABLED?

Also, why does this table not appear in the recycle bin?

SQL> select * from v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

recycling_a_constraint

COL droptime FORMAT A10
COL original_name FORMAT A10

COL owner           FORMAT A10
COL type            FORMAT A10

DROP TABLE emp;

DROP TABLE dept;

CREATE TABLE dept (

deptno NUMBER(4)

CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR2(50)
);

CREATE TABLE emp (

   empno     NUMBER(4)
      CONSTRAINT emp_pk PRIMARY KEY,

last_name VARCHAR2(50),
deptno NUMBER(4)

      CONSTRAINT emp_dept_fk
      REFERENCES dept(deptno)

);

INSERT INTO dept VALUES (10, 'Headquarters'); INSERT INTO dept VALUES (20, 'Sales');

INSERT INTO emp VALUES (101, 'Smith', 10);
INSERT INTO emp VALUES (102, 'Jones', 10);
INSERT INTO emp VALUES (103, 'Green', 20);
INSERT INTO emp VALUES (104, 'Brown', 20);

COMMIT; DROP TABLE dept CASCADE CONSTRAINTS;

PROMPT
PROMPT Is the dept table in the recycle bin? PROMPT SELECT original_name,

       object_name,
       type

FROM dba_recyclebin
WHERE original_name = 'DEPT';

PROMPT
PROMPT Is the foreign key constraint in the recycle bin? PROMPT SELECT original_name,

       object_name,
       type

FROM dba_recyclebin
WHERE can_undrop = 'YES'

AND original_name = 'EMP_DEPT_FK';

PROMPT
PROMPT Is the original foreign key constraint still active? PROMPT SELECT constraint_name, status
FROM dba_constraints
WHERE constraint_name = 'EMP_DEPT_FK';

end of script

SQL> @labs_jack/recycling_a_constraint

Table dropped.

DROP TABLE dept

*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

1 row created.

Commit complete.

Table dropped.

Is the dept table in the recycle bin?

no rows selected

Is the foreign key constraint in the recycle bin?

no rows selected

Is the original foreign key constraint still active?

CONSTRAINT_NAME                STATUS
------------------------------ --------
EMP_DEPT_FK                    ENABLED

Received on Thu Oct 13 2005 - 14:41:13 CDT