Dropping a Parent Table in a Foreign Key relationship
Administration Tips
Dropping a Parent in a Foreign Key Relationship The golden rule in Oracle is that you cannot create orphan records (that is, child records expecting to find a parent, but not able to do so). Therefore, given a table ORDERS, with a column CUSTNUM, relating to the CUSTOMERS table as its parent, the following command will fail: SQL>
DROP TABLE CUSTOMERS;
DROP TABLE CUSTOMERS
* ERROR AT LINE 1: ORA-02449: UNIQUE/PRIMARY
KEYS IN TABLE REFERENCED BY FOREIGN KEYS
What you can do, however, is use the CASCADE CONSTRAINTS clause, which will drop the foreign key constraint on the ORDERS table for you. That turns the contents of the CUSTNUM field into plain old standalone numbers that don't have any extra significance. The drop of the CUSTOMERS table can then take place without further trouble: SQL>
DROP TABLE CUSTOMERS CASCADE CONSTRAINTS;
TABLE
DROPPED.
Note that this 'cascade' clause only causes the foreign key constraint itself to be dropped, not the data in the child table. This is not the 'on delete cascade' option specified when creating the foreign key constraint in the first place (which really does delete child records when a parent record is deleted). Personally, I dislike the way the same word "cascade" means two completely different things depending on the context in which it is used, but you just have to live with it! Now, truncating the parent table would create as many orphans as dropping the table would, so the same rules apply. This command fails: SQL>
TRUNCATE TABLE CUSTOMERS;
TRUNCATE TABLE CUSTOMERS
* 1:
ERROR AT LINE ORA-02266: UNIQUE/PRIMARY
KEYS IN TABLE REFERENCED BY ENABLED FOREIGN KEYS
You will probably therefore leap to the logical conclusion that you must again use the 'CASCADE CONSTRAINTS' option. Let's try that, then: SQL>
TRUNCATE TABLE CUSTOMERS CASCADE CONSTRAINTS;
TRUNCATE TABLE CUSTOMERS CASCADE CONSTRAINTS Copyright Š Howard Rogers 2001
10/18/2001
Page 1 of 3
Dropping a Parent Table in a Foreign Key relationship
Administration Tips
* ERROR AT LINE 1: ORA-03291: INVALID
TRUNCATE OPTION
-
MISSING
STORAGE
KEYWORD
...at which point, we discover that for some unknown reason, Oracle allows 'cascade constraints' for a drop, but not for a truncate. Somewhat inconsistent, if you ask me -but again, you have to live with it. For a truncate operation to succeed, therefore, you must yourself locate the constraint and drop it (disabling it is not enough), before then being able to proceed with the truncate. This select statement will identify the constraint that needs dealing with: SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='ORDERS' AND CONSTRAINT_TYPE='R';
Note that you select for the child table name, not the parent. That will give you a report looking like this: OWNER CONSTRAINT_NAME C TABLE_NAME -------------------- ------------------------------ - ----------SCOTT CUSTNUM_FK R ORDERS Now you simply issue the following command: ALTER TABLE ORDERS DROP CONSTRAINT CUSTNUM_FK;
...and the drop of the CUSTOMERS table can proceed as normal: SQL>
DROP TABLE CUSTOMERS;
TABLE
DROPPED.
There's one other twist to this saga. It is conceivable that you might have stored the CUSTOMERS and ORDERS table in separate tablespaces. It is then conceivable that one day you might want to drop the tablespace in which the CUSTOMERS (i.e., the parent table) is housed. Since you can't drop a tablespace if it contains anything, you may dimly recall that there is an 'including contents' clause for the 'drop tablespace' command, which will automatically drop any segments contained within the tablespace for you. Try it, however, and it will fail: SQL>
DROP TABLESPACE
Copyright Š Howard Rogers 2001
PARENT
INCLUDING CONTENTS;
10/18/2001
Page 2 of 3
Dropping a Parent Table in a Foreign Key relationship
DROP TABLESPACE
PARENT
Administration Tips
INCLUDING CONTENTS
* ERROR AT LINE 1: ORA-02449: UNIQUE/PRIMARY
KEYS IN TABLE REFERENCED BY FOREIGN KEYS
The command fails, because the automatic drop of CUSTOMERS implied by the 'including contents' clause would create orphan records in the ORDERS table. The golden rule in Oracle, remember, is that you can't create orphans! But all Oracle is trying to do is drop the tables within the tablespace -and drop commands can take the 'cascade constraints' clause. So if we were to issue this command: SQL>
DROP TABLESPACE
TABLESPACE
PARENT
INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROPPED.
...the thing now works correctly.
Copyright Š Howard Rogers 2001
10/18/2001
Page 3 of 3