Posted by Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table
SQL> create table parent ( p int, constraint PAR_PK primary key (p) );
Table created.
SQL> create table child ( c int,
2 p int
3 );
Table created.
SQL> alter table child add constraint fk1 foreign key ( p ) references parent ( p );
Table altered.
That is all as we would expect, and similarly, if I inadvertently try to add the same foreign key constraint, I’ll get an error
SQL> alter table child add constraint fk2 foreign key ( p ) references parent ( p );
alter table child add constraint fk2 foreign key ( p ) references parent ( p )
*
ERROR at line 1:
ORA-02275: such a referential constraint already exists in the table
So you might be wondering, how on earth did I get myself into the following calamity on my database:
SQL> select c.constraint_name, cc.column_name, c.r_constraint_name
2 from user_constraints c,
3 user_cons_columns cc
4 where c.table_name = 'CHILD'
5 and c.constraint_type = 'R'
6 and c.constraint_name = cc.constraint_name;
CONSTRAINT_NAME COLUMN_NAME R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------------
FK1 P PAR_PK
FK2 P PAR_PK
Yes – thats TWO foreign key constraints implementing the identical check. How did I bypass the duplicate check we saw above ?
It’s just a little idiosyncrasy in all versions of Oracle since the inception of foreign key constraints. If you specify the constraints as part of the table creation DDL, the check for duplicates is skipped.
SQL> create table child ( c int,
2 p int,
3 constraint fk1 foreign key ( p ) references parent ( p ) ,
4 constraint fk2 foreign key ( p ) references parent ( p )
5 );
Table created.
It is worth looking for and rectifying this condition on your databases, because it can lead to confusing errors, for example, when you do a datapump of such a schema – the import will (correctly) fail on the second constraint, but you’ll be scratching your head when you go look at the imported result and see that the constraint is there !
This is fixed in 12c onwards.
SQL> create table child ( c int,
2 p int,
3 constraint fk1 foreign key ( p ) references parent ( p ) ,
4 constraint fk2 foreign key ( p ) references parent ( p )
5 );
constraint fk2 foreign key ( p ) references parent ( p )
*
ERROR at line 4:
ORA-02274: duplicate referential constraint specifications
Learning is not a spectator sport 