Oracle FOREIGN KEY Constraint

In the Oracle database, FOREIGN KEY Constraint is used to establish the relationship between tables. Generally, foreign key values are based on referenced primary key values only. The table has a primary key is also called the master table and the table has a foreign key is called the child table.

• One table foreign key must belong to another table primary key (or unique). Those primary key and foreign key must belong to the same data type.
• The primary key doesn’t accept duplicate, null values but the foreign key accepts duplicate and null values.

Similar to like other constraint FOREIGN KEY also can be applied at column level and table level but generally we don’t use FOREIGN KEY at table level.

FOREIGN KEY at the column level

To create FOREIGN KEY at column level we must use REFERENCES clause.

The FOREIGN KEY Syntax in Oracle,
columnName datatype(size) REFERENCES masterTable ( [primaryKeyColumnName, ….] )

Syntax to create a table with FOREIGN KEY:-
CREATE TABLE tableName(
col1 datatype(size) REFERENCES mastertableName ([primaryKeyColumnName])
);

When a table column having a foreign key, and if the column name is the same as the primaryKeyColumnName then the column name is not required. But for different column names, it is recommended to use primaryKeyColumnName.

Example:- let us first create a table with PRIMARY KEY constraint.

SQL> CREATE TABLE primarytable1(
       sno NUMBER(10) PRIMARY KEY, 
       name VARCHAR2(10)
     );
Table created.

SQL> INSERT INTO primarytable1 VALUES(1, 'aa');
SQL> INSERT INTO primarytable1 VALUES(2, 'xy');
SQL> INSERT INTO primarytable1 VALUES(3, null);
SQL> SELECT * FROM primarytable1;
       SNO NAME
---------- ----------
         1 aa
         2 xy
         3

Now create an another table with foreign key,

SQL> CREATE TABLE test(
     sno NUMBER(10) REFERENCES primarytable1);
Table created.

Both tables “primarytable1” and “test” have the same column name “sno” that’s why specifying primaryKeyColumnName after primarytable1 is not required. But if we want to give another name for the column with FOREIGN KEY then it is recommended to specify primaryKeyColumnName. Example:-

SQL> CREATE TABLE test1(id NUMBER(10) REFERENCES primarytable1(sno));
Table created.

The foreign key at table level

Actually using Foreign key at table level in real time projects is very rare concept.

Syntax:-
CREATE TABLE tablename
(
col1 datatype(size),
col2 datatype(size),
……,
FOREIGN KEY (col1, col2, …)
references masterTableName ([primaryKeyColumnName])
);

In this case number of referencing, columns must match referenced columns else Oracle gives error:- ORA-02256: number of referencing columns must match referenced columns. Example:-

SQL> CREATE TABLE primarytable2 
     ( 
       sno NUMBER(10),
       name VARCHAR2(10),
       PRIMARY KEY(sno, name)
     ); 
Table created.

SQL> CREATE TABLE test3 
     (
       sno NUMBER(10),
       name VARCHAR2(10),
       FOREIGN KEY(sno, name) 
       REFERENCES primarytable2
     );
Table created.

Whenever we are establishing relationship between tables by using foreign key constraint then oracle server violates following two rules:-

• deletion from the master table
• insertion in the child table

Deletion From Master Table

In Oracle when we try to delete records from the master table, and if those records are available in the child table then the Oracle server returns an error:- ORA-02292: integrity constraint violated – child record found

To demonstrate this, let us create a master table and child table.

SQL> CREATE TABLE master1( id NUMBER(10) PRIMARY KEY);
SQL> INSERT INTO master1 VALUES(1);
SQL> INSERT INTO master1 VALUES(2);
SQL> INSERT INTO master1 VALUES(3);
SQL> INSERT INTO master1 VALUES(4);
SQL> SELECT * FROM master1;
        ID
----------
         1
         2
         3
         4

SQL> CREATE TABLE child1( id NUMBER(10) REFERENCES master1);
SQL> INSERT INTO child1 VALUES(1);
SQL> INSERT INTO child1 VALUES(1);
SQL> INSERT INTO child1 VALUES(1);
SQL> INSERT INTO child1 VALUES(2);
SQL> INSERT INTO child1 VALUES(2);
SQL> INSERT INTO child1 VALUES(3);
SQL> INSERT INTO child1 VALUES(3);
SQL> SELECT * FROM child1;
        ID
----------
         1
         1
         1
         2
         2
         3
         3
7 rows selected.

The id=4 is not available in the child1 table so this record can be deleted from the master1 table. But id 1, 2, and 3 are available in both tables, therefore we can’t delete those records from master1 table else we will get an error:- ORA-02292: integrity constraint violated – child record found

SQL> DELETE FROM master1 WHERE id = 4;
1 row deleted.

SQL> DELETE FROM master1 WHERE id = 1;
DELETE FROM master1 WHERE id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C0014179) violated 
- child record found

To overcome this problem, if we want to delete master table record’s then we have two options,

• Manually delete all related records in the child table and then delete record from the master table
• Use ON DELETE CASCADE clause along with the foreign key constraint

Foreign key constraint supports 2 clauses.
1) ON DELETE CASCADE clause
2) ON DELETE SET NULL Clause

ON DELETE CASCADE in Oracle

This is an optional clause used along with foreign key constraints. When a child table having an ON DELETE CASCADE clause and we are deleting records of the master table then both master and child tables records will be deleted.

Syntax:-
create table tablename (
col1 datatype(size)
REFERENCES mastertablename ([primaryKeyColumnName])
ON DELETE CASCADE;
);

Note:- One master table can have multiple child tables.
Condition:- All child tables must have ON DELETE CASCADE or ON DELETE SET NULL Clause else Oracle gives error:- ORA-02292: integrity constraint violated – child record found

Example, create a child table from previously created master table “master1” using ON DELETE CASCADE clause.

SQL> CREATE TABLE child2 (
       id NUMBER(10)
       REFERENCES master1
       ON DELETE CASCADE
     );
Table created.

SQL> INSERT INTO child2 VALUES(1);
SQL> INSERT INTO child2 VALUES(1);
SQL> INSERT INTO child2 VALUES(1);
SQL> INSERT INTO child2 VALUES(2);
SQL> INSERT INTO child2 VALUES(2);
SQL> INSERT INTO child2 VALUES(3);
SQL> INSERT INTO child2 VALUES(3);
SQL> SELECT * FROM master1;
        ID
----------
         1
         2
         3

SQL> SELECT * FROM child2;
        ID
----------
         1
         1
         1
         2
         2
         3
         3
7 rows selected.

SQL> DELETE FROM master1 WHERE id=1;
ORA-02292: integrity constraint 
(SCOTT.SYS_C0014179) violated - child record found

The previous table “child1” is also a child of table “master1”, which doesn’t have an ON DELETE CASCADE clause or ON DELETE SET NULL so it is creating a problem for us. Therefore, delete the child1 table.

SQL> DROP TABLE child1;
Table dropped.

SQL> DELETE FROM master1 WHERE id=1;
1 row deleted.

SQL> SELECT * FROM master1;
        ID
----------
         2
         3

SQL> SELECT * FROM child2;
        ID
----------
         2
         2
         3
         3

ON DELETE SET NULL

Oracle also support ON DELETE SET NULL along with foreign key constraint. When a child table having this clause, and if we are deleting primary key values from the master table then automatically that primary key value deleted from the master table, and also then foreign key values are set to null within the child table.

Syntax:-
CREATE TABLE tablename
(
col1 datatype(size)
REFERENCES mastertablename ([primaryKeyColumnName])
ON DELETE SET NULL
);

Condition:- All child tables must have ON DELETE CASCADE or ON DELETE SET NULL Clause else Oracle gives error:- ORA-02292: integrity constraint violated – child record found

Example, Create a child table from “master1” table using ON DELETE SET NULL clause.

SQL> CREATE TABLE child3 (
        id NUMBER(10)
        REFERENCES master1
        ON DELETE SET NULL,
        name VARCHAR2(10)
     );
Table created.

SQL> INSERT INTO child3 VALUES(2, 'abc');
SQL> INSERT INTO child3 VALUES(2, 'xyz');
SQL> INSERT INTO child3 VALUES(3, 'abc');
SQL> SELECT * FROM master1;
        ID
----------
         2
         3

SQL> SELECT * FROM child3;
        ID NAME
---------- ----------
         2 abc
         2 xyz
         3 abc

SQL> DELETE FROM master1 WHERE id=2;
1 row deleted.

SQL> SELECT * FROM master1;
        ID
----------
         3

SQL> SELECT * FROM child3;
        ID NAME
---------- ----------
           abc
           xyz
         3 abc

Insertion in FOREIGN KEY Table

In Oracle when we try to insert other than primary key value into a table having foreign key then oracle server returns an error:- ORA-02291: integrity constraint violated – parent key not found because by default in all relational databases foreign key values are based on primary key values only.

SQL> SELECT * FROM master1;
        ID
----------
         3

SQL> INSERT INTO child2 VALUES(9);
ORA-02291: integrity constraint (SCOTT.SYS_C0014180) violated 
- parent key not found

Previously we have created tables master1 and child2. In the master1 table there is no value 9, so we can’t insert 9 on the child table.

Solution:- we must insert those values into the master table then only we can insert them into the child table.

SQL> INSERT INTO master1 VALUES(9);
1 row created.

SQL> SELECT * FROM master1;
        ID
----------
         3
         9

SQL> INSERT INTO child2 VALUES(9);
1 row created.

SQL> SELECT * FROM child2;
        ID
----------
         3
         3
         9

Add FOREIGN KEY Constraint Oracle

In all relational databases whenever we are copying a table from another table then except NOT NULL constraint all other constraints are never copied. So, we need to add constraints on the existing table or column.

We can add a FOREIGN KEY constraint on an existing column of a table or a new column in a table. In the Oracle, if we want to enable constraints on the existing table existing column then we must use a table-level syntax method, but If we want to add a new column along with constraint then we are using the column-level syntax method.

Enabling FOREIGN KEY constraint on an existing column

If we want to enable FOREIGN KEY constraint on an existing column of a table then the values of the column must exist in the master table, else we will get the error:- ORA-02298: cannot validate – parent keys not found

Syntax to enable FOREIGN KEY constraint on an existing column of a table,
ALTER TABLE tablename
ADD FOREIGN KEY (childColumnName)
REFERENCES masterTableName ([primaryKeyColumnName]);

Example:- Create a new table copied from “child2” table, then enable FOREIGN KEY constraint on an existing column of new table.

SQL> CREATE TABLE child4
     AS
     SELECT * FROM child2;
Table created.

SQL> SELECT * FROM child4;
        ID
----------
         3
         3
         9

SQL> SELECT * FROM master1;
        ID
----------
         3
         9

Since the child4 table is copied from another table so the FOREIGN KEY constraint is not copied. That’s why currently we are allowed to insert those values which don’t exist in the master table.

SQL> INSERT INTO child4 VALUES(10);
1 row created.

SQL> SELECT * FROM child4;
        ID
----------
         3
         3
         9
        10

Now, child4 table having those values which doesn’t exist in master table. Therefore, we can’t enable FOREIGN KEY constraint.

SQL> ALTER TABLE child4 
     ADD FOREIGN KEY(id) 
     REFERENCES master1;

ERROR at line 1:
ORA-02298: cannot validate (SCOTT.SYS_C0014183)
- parent keys not found

To solve this either we have to insert those values into the master table or delete them from the child table. Then only we can enable FOREIGN KEY constraint on the child table.

SQL> DELETE FROM child4
     WHERE id = 10;
1 row deleted.

SQL> ALTER TABLE child4 
     ADD FOREIGN KEY(id) 
     REFERENCES master1;
Table altered.

Adding a new column with FOREIGN KEY constraint

Syntax to add a new column with FOREIGN KEY constraint,
ALTER TABLE tablename
ADD newColumnName datatype(size)
REFERENCES masterTableName ([primaryKeyColumnName]);

Example:- Copy a table from child4 table and then add a new column to this table with FOREIGN KEY constraint.

SQL> CREATE TABLE child5
     AS
     SELECT * FROM child4;
Table created.

SQL> SELECT * FROM child5;
        ID
----------
         3
         3
         9

SQL> ALTER TABLE child5 
     ADD sno NUMBER(10) 
     REFERENCES master1;
Table altered.

SQL> SELECT * FROM child5;
        ID        SNO
---------- ----------
         3
         3
         9

The sno column is having FOREIGN KEY constraint.

TRUNCATE TABLE CASCADE clause

Oracle 12c introduced TRUNCATE TABLE ….. CASCADE clause.

Generally in Oracle, we can’t truncate the master table. To overcome this problem Oracle 12c introduced the CASCADE clause along with the TRUNCATE command, which is used to truncate the master table. It will truncate both master and child tables.

Condition:- Before using this clause, the child table must have ON DELETE CASCADE clause.

Syntax:- TRUNCATE TABLE tablename CASCADE;

SQL> CREATE TABLE master2 (
       sno NUMBER(10) PRIMARY KEY
     );
SQL> INSERT INTO master2 VALUES(1);
SQL> SELECT * FROM master2;
       SNO
----------
         1

SQL> CREATE TABLE child6 (
       sno NUMBER(10)
       REFERENCES master2
       ON DELETE CASCADE
     );
SQL> INSERT INTO child6 VALUES(1);
SQL> INSERT INTO child6 VALUES(1);
SQL> SELECT * FROM child6;
       SNO
----------
         1
         1

SQL> TRUNCATE TABLE master2;
ORA-02266: unique/primary keys in table
referenced by enabled foreign keys

SQL> TRUNCATE TABLE master2 CASCADE;
Table truncated.

SQL> SELECT * FROM master2;
no data found

SQL> SELECT * FROM child6;
no data found

Note:- Generally, we can’t drop the primary key along with the referenced key. To overcome this problem, the Oracle provided CASCADE clause along with ALTER DROP, which is used to drop the primary key.

Syntax:- ALTER TABLE tablename DROP PRIMARY KEY CASCADE;

Example:-

SQL> ALTER TABLE master2 DROP PRIMARY KEY CASCADE;
Table altered.

Assigning User-defined Name to Foreign key Constraint

In the Oracle database, whenever we are creating a constraint then the Oracle server internally automatically generates a unique identification number for identifying a constraint uniquely in the format of SYS_Cn, this is also called a pre-defined constraint name. Example:-

SQL> CREATE TABLE child7 (
       id NUMBER(10) REFERENCES master1
     );
Table created.

SQL> SELECT * FROM master1;
        ID
----------
         3
         9

SQL> INSERT INTO child7 VALUES(5);
ORA-02291: integrity constraint (SCOTT.SYS_C0014189) violated 
- parent key not found

Here SYS_C0014189 is the Oracle generated unique identification number of constraints applied on the id column of the child7 table. We can get this information from user_cons_columns.

SQL> DESC user_cons_columns;
 Name                 Null?    Type
 -------------------- -------- ----------------------------
 OWNER                NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME      NOT NULL VARCHAR2(30)
 TABLE_NAME           NOT NULL VARCHAR2(30)
 COLUMN_NAME                   VARCHAR2(4000)
 POSITION                      NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'CHILD7';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
ID              SYS_C0014189

Note that in the above query table name CHILD7 is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table are case sensitive, and here U2 is a record of user_cons_columns.

When Constraint is enabled at table level then the pre-defined constraint name will be same of those columns.

The pre-defined constraint name can create confusion for others and just by seeing the pre-defined constraint name, we can’t identify which constraint is enabled on this column. In place of a pre-defined constraint name, we can also assign our own name by using the CONSTRAINT keyword by using the following syntax.

Syntax:- CONSTRAINT user-definedname constriantType

Example:- Create a new table in Oracle with user-defined FOREIGN KEY constraint name “foreign_sno”.

SQL> CREATE TABLE child8 (
       sno NUMBER(10)
       CONSTRAINT foreign_sno
       REFERENCES master1
     );
Table created.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'CHILD8';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
ID              FOREIGN_SNO

Note:- The user-defined constraint name must be unique within a user.

Disable FOREIGN KEY Constraint Oracle

Syntax to disable constraint:-
ALTER TABLE tableName
DISABLE CONSTRAINTS constraint_name;

Example:-

SQL> SELECT * from master1;
        ID
----------
         3
         9

SQL> INSERT INTO child8 VALUES(10);
ORA-02291: integrity constraint (SCOTT.FOREIGN_SNO) violated
 - parent key not
found


SQL> ALTER TABLE child8
     DISABLE CONSTRAINT FOREIGN_SNO;
Table altered.

SQL> INSERT INTO child8 VALUES(10);
1 row created.

SQL> SELECT * FROM child8;
       SNO
----------
        10

Enable FOREIGN KEY Constraint

Syntax to enable constraint:-
ALTER TABLE tableName
ENABLE CONSTRAINTS constraint_name;

Condition:- The child table columns must not violet the foreign key conditions i.e. child table column values must be there in the master table.

SQL> ALTER TABLE child8
     ENABLE CONSTRAINT FOREIGN_SNO;
ORA-02298: cannot validate (SCOTT.FOREIGN_SNO)
 - parent keys not found

SQL> DELETE FROM child8 where sno = 10;
1 row deleted.

SQL> ALTER TABLE child8
     ENABLE CONSTRAINT FOREIGN_SNO;
Table altered.

Drop FOREIGN KEY Constraint Oracle

We can drop constraint by using its name. The syntax to drop or remove the constraint from the table is,
ALTER TABLE tableName
DROP CONSTRAINTS constraint_name;

Example:-

SQL> ALTER TABLE child7 
     DROP CONSTRAINTS SYS_C0014189;
Table altered.

Using user-defined constraint name,

SQL> ALTER TABLE child8 
     DROP CONSTRAINTS FOREIGN_SNO;
Table altered.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint

Search