What happens when you drop a primary key constraint that has a foreign key attached to it in Oracle and then re-enable the primary key?

Question

Okay so say you have 2 tables, Table1 and Table2.

Table1's primary key is the foreign key to Table2. If you drop the primary key constraint on Table1 and then re-enable it with Alter Table command, would the foreign key on Table2 automatically be re-enabled also?

Answer 1

Let's try what happens.

First create tables t1 and t2.

CREATE TABLE t1 (parent_id NUMBER);
Table created
CREATE TABLE t2 (child_id NUMBER);
Table created

and then create the 2 constraints

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (parent_id) USING INDEX;
Table altered
ALTER TABLE t2 ADD CONSTRAINT t2_fk FOREIGN KEY (child_id) REFERENCES t1;
Table altered

now look at the dictionary and select the constraints of the 2 tables

SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.R_CONSTRAINT_NAME
FROM User_Constraints c WHERE c.TABLE_NAME IN ('T1', 'T2');
CONSTRAINT_NAME                CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------
T1_PK                          P               
T2_FK                          R               T1_PK

we see 1 primary key (P) and 1 foreign key (R) Then drop the pk

ALTER TABLE t1 DROP CONSTRAINT t1_pk CASCADE;
Table altered

the cascade keyword instructs oracle to implicitly drop all fk constraints referencing the pk to drop. Without cascade you will get ORA-02273: this unique/primary key is referenced by some foreign keys and the pk is not dropped.

again look at the dictionary and select the constraints of the 2 tables

SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.R_CONSTRAINT_NAME
FROM User_Constraints c WHERE c.TABLE_NAME IN ('T1', 'T2');
CONSTRAINT_NAME                CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------

no rows - pk and fk are gone. recreate pk

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (parent_id) USING INDEX;
Table altered

and look at the dictionary

SELECT c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE, c.R_CONSTRAINT_NAME
FROM User_Constraints c WHERE c.TABLE_NAME IN ('T1', 'T2');
CONSTRAINT_NAME                CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ --------------- ------------------------------
T1_PK                          P               

only the pk is created. cleanup testcase

DROP TABLE t2;
Table dropped
DROP TABLE t1;
Table dropped