Drop PRIMARY KEY constraint

1. create the test tables with primary key and foreign key

SYS@testdb> CONN user1
Connected.

user1@testdb>
user1@testdb> create table emp (emp_id number constraint emp_pk primary key );

Table created.

user1@testdb> create table empinfo ( emp_name varchar2(10), emp_id number references emp(emp_id));

Table created.

user1@testdb> drop table emp;
drop table emp
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys



user1@testdb> insert into emp values(1);

1 row created.

user1@testdb> insert into empinfo values ('li',2);
insert into empinfo values ('li',2)
*
ERROR at line 1:
ORA-02291: integrity constraint (user1.SYS_C0044749) violated - parent key
not found


user1@testdb>  insert into empinfo values ('li',1);

1 row created.

user1@testdb> commit;

Commit complete.


2. attemp to drop parent table emp, even failed after diable both primary and foreign key constraints.

user1@testdb> drop table emp;
drop table emp
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys




user1@testdb> select table_name, constraint_name, constraint_type, r_constraint_name, status from dba_constraints
  2  where table_name in ('EMP','EMPINFO');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
R_CONSTRAINT_NAME              STATUS
------------------------------ --------
EMPINFO                        SYS_C0044749                   R
EMP_PK                         ENABLED

EMP                            EMP_PK                         P
                               ENABLED


user1@testdb>  alter table empinfo disable constraint SYS_C0044749 ;

Table altered.

user1@testdb> drop table emp;
drop table emp
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


user1@testdb>  alter table emp  disable constraint EMP_PK;

Table altered.

user1@testdb>  drop table emp;
 drop table emp
            *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


user1@testdb>  drop table emp cascade constraint;

Table dropped.

user1@testdb> select table_name, constraint_name, constraint_type, r_constraint_name, status from dba_constraints where table_name in ('EMP','EMPINFO');

no rows selected

user1@testdb> select * from empinfo;

EMP_NAME       EMP_ID
---------- ----------
li                  1


3. create backup the parent table after changes made to it. e.g partition it.

user1@testdb>  create table emp (emp_id number constraint emp_pk primary key );

Table created.



user1@testdb>  alter table empinfo add constraint empinfo_fk foreign key (emp_id) references emp(emp_id);
 alter table empinfo add constraint empinfo_fk foreign key (emp_id) references emp(emp_id)
                                    *
ERROR at line 1:
ORA-02298: cannot validate (user1.EMPINFO_FK) - parent keys not found


user1@testdb> insert into emp values(1);

1 row created.

user1@testdb>  alter table empinfo add constraint empinfo_fk foreign key (emp_id) references emp(emp_id);

Table altered.

user1@testdb> select table_name, constraint_name, constraint_type, r_constraint_name, status from dba_constraints
  2   where table_name in ('EMP','EMPINFO');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
R_CONSTRAINT_NAME              STATUS
------------------------------ --------
EMPINFO                        EMPINFO_FK                     R
EMP_PK                         ENABLED

EMP                            EMP_PK                         P
                               ENABLED


Conclusion:

drop table with cascade constraint is the must.