Finding Foreign Key Constraints in Oracle

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.

I tried to
add a primary key index on the parent table TEMP_JP1.COL1. Duplicate primary keys are not allowed in the parent table.


SQL> alter table temp_jp1 add constraint temp_jp1_pk primary key(col2);
alter table temp_jp1 add constraint temp_jp1_pk primary key(col2)
*
ERROR at line 1:
ORA-02260: table can have only one primary key

Ok, now I will
add a unique key constraint on parent table TEMP_JP1’s

Col2. As
the errors say, a table can have only one primary key constraint. A table can
have multiple unique key constraints.

SQL> alter table temp_jp1 add constraint temp_jp1_Uk unique (col2);

We are good
to add a foreign key constraint on TEMP_JP3 table.


SQL> alter table temp_jp3 add(constraint temp_jp3_fk
2 foreign key(col2) references temp_jp1(col2));
Table altered.

Let us
insert a row into TEMP_JP3 table.


SQL> insert into temp_jp3 values(1);
insert into temp_jp3 values(1)
*
ERROR at line 1:
ORA-02291: integrity constraint (JP.TEMP_JP3_FK) violated – parent key not
found

The
enabled foreign key constraint will not allow inserting child records in the
table, unless a matching record is found in the parent table.


SQL> insert into temp_jp3 values(2);
1 row created.
SQL> commit;
Commit complete.

In essence,
constraints safeguard and validate the data.

Each parent
record can have multiple child records, but each child can relate to ONLY one
parent record. That is why Oracle wants a PRIMARY/ UNIQUE KEY index created on
the PRIMARY KEY column of the PARENT TABLE. It is not mandatory to have such an
index created on the CHILD KEY column in the CHILD TABLE.

In our
case, I could not enable foreign key constraints on TEMP_JP2 and TEMP_JP3 tables,
unless I first added a PRIMARY KEY constraint on TEMP_JP1.COL1 and a UNIQUE
KEY constraint on TEMP_JP1.COL2.

Now let us
remove the records from the parent table TEMP_JP1.


SQL> truncate table temp_jp1;
truncate table temp_jp1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> Delete from temp_jp1;
Delete from temp_jp1
*
ERROR at line 1:
ORA-02292: integrity constraint (JP.TEMP_JP3_FK) violated – child record
found

The
parent table can not be truncated, nor deleted, when foreign key(referential integrity) constraints are referencing them.

From
the data dictionary view all_constrains, I will start my investigation.


SQL> desc all_constraints
Name Null? Type
—————————————– ——– —————————-
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)

Remember,
when we tried to truncate the table TEMP_JP1, we received an error that said:

“ORA-02266: unique/primary keys in table referenced by enabled foreign keys”

Let
us find out which constraints are enabled on our parent table TEMP_JP1.


SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
2 from all_constraints where constraint_type in (‘P’,’U’) and table_name=’TEMP_JP1′;
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
———- ————— – ———- ———- ——————–
JP TEMP_JP1_PK P TEMP_JP1
JP TEMP_JP1_UK U TEMP_JP1

Now
we know the primary/unique key constraints enabled on our parent table, TEMP_JP1.

The
r_constraint_name( primary/unique constraint name in the parent table) column
in the all_constraints view is referenced by the constraint_name (foreign
key(referential integrity)
constraint in the child table), when the
constraint_type is ‘R’. Using this definition, let us find out all the foreign
key(referential integrity) constraints referencing the TEMP_JP1 table:


SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
2 from all_constraints
3 where constraint_type=’R’
4 and r_constraint_name in (select constraint_name from all_constraints
5 where constraint_type in (‘P’,’U’) and table_name=’TEMP_JP1′);
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
———- ————— – ———- ———- ——————–
JP TEMP_JP2_FK R TEMP_JP2 JP TEMP_JP1_PK
JP TEMP_JP3_FK R TEMP_JP3 JP TEMP_JP1_UK

Here in my
sub-query, I am supplying ONLY the primary and unique key constraint types with
the qualifier “where constraint_type in (‘P’,’U’) “, as we had seen earlier,
that foreign key constraints can only be enabled on a child table, when a PRIMARY/UNIQUE
KEY constraint is enabled on the parent table.

We
had identified the foreign key constraints that are referencing the parent
table TEMP_JP1.

Let
us try to disable the primary/unique key constraints on the table.


SQL> alter table temp_jp1 disable constraint temp_jp1_pk;
alter table temp_jp1 disable constraint temp_jp1_pk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (JP.TEMP_JP1_PK) – dependencies exist

Let us
disable the unique key constraint from our parent table, TEMP_JP1.


SQL> alter table temp_jp1 disable constraint temp_jp1_uk;
alter table temp_jp1 disable constraint temp_jp1_uk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (JP.TEMP_JP1_UK) – dependencies exist

Now we understand
that as long as the foreign key constraints are enabled on the child tables, the
data in the parent table is protected.

If we need
to manipulate the data in the parent table, TEMP_JP1, first disable the foreign key(referential integrity) constraints on
the child tables that are referencing the parent table.

Now
let us disable the foreign key(referential integrity) constraints
on the child tables, identified from our earlier query.


SQL> alter table temp_jp2 disable constraint temp_jp2_fk;
Table altered.
SQL> alter table temp_jp3 disable constraint temp_jp3_fk;
Table altered.
SQL> truncate table temp_jp1;
Table truncated.

We
could successfully truncate the parent table, after disabling the foreign key
constraints on the child tables, that were referencing the parent table.

A
ready built script to identify and disable/enable foreign key constraints on
child tables:


SQL> select ‘alter table ‘||a.owner||’.’||a.table_name||
2 ‘ disable constraint ‘||a.constraint_name||’;’
3 from all_constraints a, all_constraints b
4 where a.constraint_type = ‘R’
5 and a.r_constraint_name = b.constraint_name
6 and a.r_owner = b.owner
7 and b.table_name = ‘TEMP_JP1’;
‘ALTERTABLE’||A.OWNER||’.’||A.TABLE_NAME||’DISABLECONSTRAINT’||A.CONSTRAINT_NAME
——————————————————————————–
alter table JP.TEMP_JP3 disable constraint TEMP_JP3_FK;
alter table JP.TEMP_JP2 disable constraint TEMP_JP2_FK;

I hope this
narrative has given a clear picture about foreign key constraints and how to
handle the problems, while manipulating the data in the parent table.

For the
experienced:


SQL> INSERT INTO TEMP_JP1 VALUES(2,NULL);
1 row created.
SQL> INSERT INTO TEMP_JP3 VALUES(NULL);
1 row created.
SQL> INSERT INTO TEMP_JP3 VALUES(NULL);
1 row created.
SQL> insert into temp_jp1 values(null,null);
insert into temp_jp1 values(null,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“JP”.”TEMP_JP1″.”COL1″)

Please
be careful: Both Primary / Unique key indexesdo not allow duplicates in the
column. All primary key indexes are unique. A table can have only one Primary
key index and can have multiple unique key indexes. One main difference is that
you can not insert NULL values into a primary key constraint enabled column,
whereas you can insert NULL values into a unique key constraint enabled column.

For
this reason, if your parent key in the parent table is indexed with a unique
key constraint, then enable NOT NULL constraint on the parent key column.


SQL> alter table temp_jp1 modify(col2 not null);
Table altered.
SQL> insert into temp_jp1 values(2,null);
insert into temp_jp1 values(2,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“JP”.”TEMP_JP1″.”COL2″)

JP Vijaykumar

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles