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