Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 20, 2007

Finding Foreign Key Constraints in Oracle

By DatabaseJournal.com Staff

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM