ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

If you want to add RELY DISABLE constraint to child table that references a master table with primary key constraint with NORELY flag you can encounter following error:

ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

Example:

create table master_tbl
(id number primary key);

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name='MASTER_TBL';

CONSTRAINT_NAME     CONSTRAINT_TYPE TABLE_NAME    RELY
------------------- --------------- ------------- ----
SYS_C0012428928                   P MASTER_TBL        
create table child_tbl
(id number);

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl rely disable;

ORA-25158: Cannot specify RELY for foreign key 
           if the associated primary key is NORELY

First option is to modify primary key constraint of table master_tbl to RELY but there is another workaround – just add DISABLE RELY constraint in two steps to child_tbl.

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl disable;

alter table child_tbl
modify constraint child_tbl_fk rely;

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name in ('MASTER_TBL', 'CHILD_TBL');

CONSTRAINT_NAME   CONSTRAINT_TYPE TABLE_NAME     RELY
----------------- --------------- -------------- ----
CHILD_TBL_FK      R               CHILD_TBL      RELY
SYS_C0012428928   P               MASTER_TBL

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.