ORA-02297: cannot disable primary key constraint - dependencies exist

These happens if you want to disable a primary key constraints when the child foreign key exits

let us see the demonstration:
i will create a table with primary key:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);

Now let us make a foreign key relationship depending on this table:
sys@LION> create table opt (id number(30),emp_mail varchar2(50),constraint opt_id_fk foreign key(id) references ersh_tab(emp_id));

Now i will try to disable the primary key constraint , which is ersh_id_pk:
sys@LION> alter table ersh_tab disable constraints emp_id_pk;
alter table ersh_tab disable constraints emp_id_pk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SYS.EMP_ID_PK) - dependencies exist

You cannot disable a parent key constraints if the foreign key exist. If you want to do so , you need to first disable or drop that foreign key constraint first.
sys@LION> alter table opt disable constraint opt_id_fk;
Table altered.
then disable the primary key
sys@LION> alter table ersh_tab disable constraints emp_id_pk;
Table altered.

you can get the dependency information from dba_constraints/user_constraints/all_constraints view .


thanks ,
have a nice day ..............

To find what you seek in the road of life,
the best proverb of all is that which says:
"Leave no stone unturned."
Edward Bulwer Lytton