oracleskill

by JP Vijaykumar

Many times developers find it difficult to delete records from parent tables when child records reference those tables through foreign key (referential integrity) constraints.

Constraints validate the data; without constraints, we are just storing invalid data.

For a developer to identify and disable foreign key constraints is a difficult task. Most of the time, the application's ER diagrams are not available to the developers. A brief description on the foreign key (referential integrity) constraints will go a long way in identifying and disabling these constraints.

The details of the three tables I created in this document are:

TEMP_JP1
    

PARENT TABLE

TEMP_JP2
    
CHILD TABLE

TEMP_JP3
    

CHILD TABLE

Child tables TEMP_JP2 and TEMP_JP3 reference the parent table TEMP_JP1

I created a table TEMP_JP1, inserted one row.

create table temp_jp1(col1 number,col2 number);

insert into temp_jp1 values(1,2);

commit;

I created a second table TEMP_JP2. When I tried to create a foreign key(referential integrity) constraint on the second table, I received an error.

create table temp_jp2(col1 number);

SQL> alter table temp_jp2 add (constraint temp_jp2_fk

2 foreign key (col1) references temp_jp1(col1));

foreign key (col1) references temp_jp1(col1))

*

ERROR at line 2:

ORA-02270: no matching unique or primary key for this column-list
     

Unless a primary/unique key constraint is enabled on the parent key column, Oracle does not allow enabling a foreign key constraint on a child key column. Primary/unique keys on the parent key column will not allow duplicate values.

I created primary key index on the parent table TEMP_JP1(COL1).

alter table temp_jp1 add constraint temp_jp1_pk primary key(col1);

I created foreign key(referential integrity) constraint on the second table successfully and inserted one row.

SQL> alter table temp_jp2 add (constraint temp_jp2_fk

2 foreign key (col1) references temp_jp1(col1));

Table altered.

insert into temp_jp2 values(1);

commit;

For demo purposes, I created a third table TEMP_JP3.

create table temp_jp3(col2 number);

I tried to add a foreign key(referential integrity) constraint to the third table, TEMP_JP3.

SQL> alter table temp_jp3 add(constraint temp_jp3_fk

2 foreign key(col2) references temp_jp1(col2));

foreign key(col2) references temp_jp1(col2))

*

ERROR at line 2:

ORA-02270: no matching unique or primary key for this column-list

Again, make sure there are no duplicates in the parent key column before enabling foreign key(referential integrity constraints on a child key column.