Oracle Add Foreign Key

by
Oracle add foreign key constraint enable novalidate

In this Oracle tutorial, we will learn how to add foreign key constraints to the oracle database. We are currently working on oracle 19c but this tutorial will work for all the versions of the oracle database. Here is the list of topics that we are going to discuss.

  • Oracle how to add foreign key constraint
  • Oracle db ad foreign key constraint
  • Oracle set foreign key constraint
  • Oracle add column foreign key constraint
  • Oracle add foreign key constraint using index
  • Oracle add foreign key constraint on delete cascade
  • Oracle add foreign key constraint enable novalidate
  • How to add foreign key constraint in oracle sql developer
  • Oracle add foreing key constraint to existing table

Oracle how to add foreign key constraint

The primary key of one table when used in other tables then it is referenced as a foreign key in that other table. Foreign key in Oracle binds relationship with other tables in oracle database.

  • Your personal information include Name, gender, date of birth, address, government id, driving license, etc. Here, government id and driving license works as a primary key.
  • Your professional information include Name, gender, date of joining, company unique id, designation, etc.
  • While joining the organization you must have submitted your personal details. Government ID will be referenced as a foreign key in your company employment table.
  • Foreign key is the counterfeit of primary key. Also, null values are allowed in foreign key.
  • To add foreign key constraint it is important that you have at least 2 tables in oracle database.

Syntax:

Here, is the syntax for adding foreign key constraints in the oracle database. The parent table is the one whose primary key column we are using as a foreign key here.


CREATE TABLE <table_name>(
    col_1 [data type],
    col_2 [data type],
    CONSTRAINT fk_NAME FOREIGN KEY (col_2),
    REFERENCE <parent_table_name>(col)
    ON DELETE CASCADE
);

Example:

In our example, we have created two tables one holds the personal information and the other has employment details. The table with the employment details uses the primary key of the personal info table.

-- CREATE PARENT TABLE
CREATE TABLE PERSONAL_INFO(
    FULL_NAME VARCHAR2(100),
    GENDER VARCHAR2(10),
    DOB DATE,
    GOVT_ID INTEGER PRIMARY KEY,
    ADDRESS VARCHAR2(100)
);

-- CREATE CHILD TABLE
CREATE TABLE EMP_DETAILS(
    EMP_ID INTEGER PRIMARY KEY,
    NAME VARCHAR2(100),
    DOJ DATE,
    DOE DATE DEFAULT NULL,
    DEPARTMENT VARCHAR2(100),
    ID_PROOF INTEGER,
    CONSTRAINT FK_EMP_DETAILS FOREIGN KEY(ID_PROOF)
    REFERENCES PERSONAL_INFO(GOVT_ID)
    ON DELETE CASCADE
);

Also, check: Oracle add primary key

Oracle add foreign key constraint using index

As per the Oracle Official Documentation, it is not possible to add foreign key constraints using an index. This documentation is valid till the latest version of the oracle database i.e 21c. We will update the blog if there are changes in the future.

Oracle add foreign key constraint on delete cascade

Foreign key in Oracle creates a relationship between two or more tables. Once the data is entered you can change it neither in the parent table nor in the child table(s).

Even if you will try to delete the data it won’t allow you because you are about to make changes on the one table but the same data is being shared between two or more other tables as well.

  • Oracle do not facilitate the auto update feature, you cannot edit data in one table & same data will be updated automatically in other tables as well.
  • To update or remove the value from the parent table you have to drop foreign key constraint from all the other tables and no dba wants to face that situation.
  • To overcome this situation, oracle provide On Delete Cascade method in oracle database.
  • Add this line, every time you are creating the foreign key constraint this will help you later when you will try to remove any value from the parent table then that value will be removed from other tables as well.

Example:

In our example, we have shown how to add a foreign key constraint on the delete cascade in the oracle database. At the end of the second table add the On Delete Cascade keyword after References.

-- CREATE PARENT TABLE
CREATE TABLE PERSONAL_INFO(
    FULL_NAME VARCHAR2(100),
    GENDER VARCHAR2(10),
    DOB DATE,
    GOVT_ID INTEGER PRIMARY KEY,
    ADDRESS VARCHAR2(100)
);

-- CREATE CHILD TABLE
CREATE TABLE EMP_DETAILS(
    EMP_ID INTEGER PRIMARY KEY,
    NAME VARCHAR2(100),
    DOJ DATE,
    DOE DATE DEFAULT NULL,
    DEPARTMENT VARCHAR2(100),
    ID_PROOF INTEGER,
    CONSTRAINT FK_EMP_DETAILS FOREIGN KEY(ID_PROOF)
    REFERENCES PERSONAL_INFO(GOVT_ID)
    ON DELETE CASCADE
);

Foreign key creates a relationship between two tables in the oracle database. It constrains table B (child table) to use options only available in table A (parent table).

If you are trying to delete the parent table and getting the below error:

Error report -
ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 -  "unique/primary keys in table referenced by foreign keys"
*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.
*Action:   Before performing the above operations the table, drop the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

This means that oracle is not allowing you to delete the parent table from the database because the column of the parent table is being used in another table(s) (child table).

At that time, using the on delete cascade keyword table can be deleted and the foreign key column will also be removed from the child table in the oracle database.

On delete, must be applied while creating foreign key in oracle database. Using Alter table statement, on delete can be applied to the existing table in oracle.

Below is the syntax to add on delete cascade to a foreign key constraint in oracle:

-- ADD ON DELETE CASCADE DURING TABLE CREATION
CREATE TABLE <child_table_name>(
    column1 <Data type> [constraints],
    column2 <Data type> [constraints],
    column3 <Data type> [constraints],
    column4 <Data type> [constraints],
    CONSTRAINT FK_child_table FOREIGN KEY (column2)
    REFERENCES parent_table_name(parent_table_col) On Delete Cascade;

-- ADD ON DELETE CASCADE IN EXSISTING TABLE
ALTER TABLE <child_table_name>
DROP CONSTRAINT FK_child_table;

ALTER TABLE <child_table_name>
ADD CONSTRAINT FK_child_table FOREIGN KEY(chil_table_column)
REFERENCES PARENT_TABLE_NAME(parent_table_col) On Delete Cascade;

Example:

So far we have learned about on delete cascade in the oracle database. Here, we will see an example for the same.

  • We have shared the script that will create two tables, insert data in both, and then tries to delete the parent table.
  • The above mentioned error will appear because foreign key was not applied with on delete cascade option.
  • The next script will add the on delete cascade to foreign key constraint in oracle table.
  • Last script will try agian to delete the table and this time table will be deleted successfully.
  • For better experience run each script one after another. Also, read the comments to understand the workflow.
-- CREATE MANAGER TABLE
CREATE TABLE HOTEL_MANAGERS(
    MANAGER_ID INTEGER,
    MANAGER_NAME VARCHAR2(100),
    CONSTRAINT PK_HOTEL_MANAGERS PRIMARY KEY(MANAGER_NAME)
);

-- CREATE HOTEL TABLE
CREATE TABLE FRANKLIN_HOTELS(
    HOTEL_ID INTEGER,
    HOTEL_NAME VARCHAR2(100), 
    HOTEL_LOCATION VARCHAR2(100),
    HOTEL_MANAGER VARCHAR2(100),
    HOTEL_RATINGS FLOAT,
    CONSTRAINT FK_FRANKLIN_HOTELS FOREIGN KEY(HOTEL_MANAGER)
    REFERENCES HOTEL_MANAGERS(MANAGER_NAME)
);

-- INSERT MANAGER DETAILS
INSERT INTO HOTEL_MANAGERS 
VALUES(101, 'JAMES');

INSERT INTO HOTEL_MANAGERS 
VALUES(102, 'Johnson');

INSERT INTO HOTEL_MANAGERS 
VALUES(103, 'SMITH');

INSERT INTO HOTEL_MANAGERS 
VALUES(104, 'MILER');

-- INSERT HOTEL DETAILS
INSERT INTO FRANKLIN_HOTELS
VALUES(801, 'NEW YORK HILTON', 'NEW YORK', 'SMITH', 4.5);

INSERT INTO FRANKLIN_HOTELS
VALUES(802, 'THE OLD WEST INN', 'CALIFORNIA', 'MILER', 4.2);

INSERT INTO FRANKLIN_HOTELS
VALUES(803, 'Hotel Philadelphia', 'Philadelphia', 'JAMES', 4.7);

INSERT INTO FRANKLIN_HOTELS
VALUES(804, 'Luxor Hotel and Casino', 'LAS VEGAS', 'Johnson', 4.2);

-- VIEW DATA
SELECT * FROM FRANKLIN_HOTELS;
SELECT * FROM HOTEL_MANAGERS;

-- DROP PARENT TABLE [FAILED ATTEMPT]
DROP TABLE HOTEL_MANAGERS;

-- DROP FOREIGN KEY
ALTER TABLE FRANKLIN_HOTELS
DROP CONSTRAINT FK_FRANKLIN_HOTELS;

-- ADD FOREIGN KEY WITH ON DELETE CASCADE
ALTER TABLE FRANKLIN_HOTELS
ADD CONSTRAINT FK_FRANKLIN_HOTELS FOREIGN KEY(HOTEL_MANAGER)
REFERENCES HOTEL_MANAGERS(MANAGER_NAME) ON DELETE CASCADE;

-- DROP PARENT TABLE [SUCCESSFUL ATTEMPT]
DROP TABLE HOTEL_MANAGERS;

Read: How to Check Oracle Database Version

Oracle add foreign key constraint enable novalidate

In an Oracle database, if you are trying to add a foreign key on a table with existing data in that case, if data is incorrect that program won’t allow you to create a foreign key and threw below error.

Error report -
ORA-02298: cannot validate (SYS.FK_TENANTS) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"

Enable Novalidate method ignores the existing incorrect data in the table and creates a foreign key anyways.

Example:

In our example, we have assumed a scenario of a Rent database where one table holds the information about room category and rent whereas another table contains the information of tenants.

Oracle add foreign key constraint enable novalidate
Oracle add foreign key constraint enable novalidate
  • Based upon the above table, suppose foreign key is not yet added in these tables.
  • Now if the Room category B is removed by the landlord as a result of which now child table contains incorrect values because Room B102 and B101 also removed.
  • If you will try to create a foreign key now, it will throw error.
  • You want to ignore the previous data and create the foreign key and wish to apply the changes on future inputs.
  • That is where Enable Novalidate comes into picture. By adding this statement, you will be able to create foreign key even with the incorrect data.
  • Below we have shared the script of the above table and tried to demonstrate our scenario. 
-- CREATE ROOM TABLE
CREATE TABLE ROOM(
    ROOM_CATEGORY VARCHAR2(1) PRIMARY KEY,
    RENT FLOAT
);

-- INSERT ROOM DATA
INSERT INTO ROOM 
VALUES ('A', 2000);

INSERT INTO ROOM 
VALUES ('B', 2500);

INSERT INTO ROOM 
VALUES ('C', 3000);

INSERT INTO ROOM 
VALUES ('D', 4000);


-- CREATE TENANTS TABLE
CREATE TABLE TENANTS(
    ID_PROOF VARCHAR2(100) PRIMARY KEY,
    TENANT_NAME VARCHAR2(100),
    MOBILE INTEGER,
    ROOM VARCHAR2(10),
    ROOM_TYPE VARCHAR2(1)
);

-- INSERT TENANT DATA
INSERT INTO TENANTS
VALUES('A1111', 'JENNIFER A. OVERSTREET', 2022305545, 'B102', 'B');

INSERT INTO TENANTS
VALUES('A1112', 'ELAINA S. BRYANT', 9038799709, 'D103', 'D');

INSERT INTO TENANTS
VALUES('A2123', 'KENNETH D. GREEN', 2316939924, 'B101', 'B');

INSERT INTO TENANTS
VALUES('A3123', 'JOHN K. KOHR', 7043130039, 'A105', 'A');

-- VIEW DATA
SELECT * FROM TENANTS;
SELECT * FROM ROOM;

-- DELETE CATEGORY B
DELETE FROM ROOM
WHERE ROOM_CATEGORY='B';

-- ADDING FOREIGN KEY [ERROR IS EXPECTED]
ALTER TABLE TENANTS
ADD CONSTRAINT FK_TENANTS FOREIGN KEY(ROOM_TYPE)
REFERENCES ROOM(ROOM_CATEGORY);

-- ADD FOREIGN KEY CONSTRAINT WITH ENABLE NOVALIDATE
ALTER TABLE TENANTS
ADD CONSTRAINT FK_TENANTS FOREIGN KEY (ROOM_TYPE)
REFERENCES ROOM(ROOM_CATEGORY) ENABLE NOVALIDATE;

Read: How to Get List all Tables in Oracle Database

How to add foreign key constraint in oracle sql developer

Oracle SQL developer tool allows running of oracle database queries using both graphical user interface and command line. It is capable of handling very large queries of the oracle database all the versions.

  • In this section, we will learn how to add foreign key constraints in the Oracle SQL developer tool.
  • Since we have already discussed how to add foreign key constraint with a statement or command line in other sections of the tutorial.
  • So in this, we will add a foreign key constraint using Graphical User Interface (GUI) of oracle sql developer tool.

Steps:

Here are the steps to add foreign key constraints in the Oracle SQL developer tool.

oracle database add foreign key using sql developer using GUI
oracle database add foreign key using SQL developer using GUI
  1. Right-click on the table -> Go to Constraints -> Select Foreign Key
  2. Constraint Name: Provide a name to foreign key constraint, later this name will be helpful in referring this foreign key.
  3. Column Name: Select the column name from the dropdown on which you want to add foreign key constraint.
  4. References Table Name: Select the parent table name from the dropdown menu.
  5. Referencing Column: Select the column name from the parent table.

Read: Connect Excel to Oracle Database

Oracle add foreign key constraint to existing table

Using Alter Table statement,t we can add foreign key constraints to an existing table. In case you are getting the below error that means both the columns of (child and parent table) don’t have similar data for the primary key column.

Error report -
ORA-02298: cannot validate (SYS.FK_TENANTS) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"

In that case either you can edit the data of the parent table as mentioned in the child table or you can enable novalidate method. We have demonstrated enable novalidate method in our above section – Oracle add foreign key constraint enable novalidate.

You may also like to read the following Oracle tutorials.

In this tutorial, we have learned how to add foreign key constraints to the oracle database. Also, we have covered these topics.

  • Oracle how to add foreign key constraint
  • Oracle db ad foreign key constraint
  • Oracle set foreign key constraint
  • Oracle add column foreign key constraint
  • Oracle add foreign key constraint using index
  • Oracle add foreign key constraint on delete cascade
  • Oracle add foreign key constraint enable novalidate
  • How to add foreign key constraint in oracle sql developer
  • Oracle add foreing key constraint to existing table