FOREIGN KEY Constraint in Oracle

FOREIGN KEY Constraint in Oracle

• FOREIGN KEY is a referential integrity constraint.
• It establishes a relationship between PRIMARY KEY or UNIQUE of the same table or a different table
• It can be created on a single column or it can be composite key
• It can be defined at column level or table level

Types of FOREIGN KEY Constraints:

• SINGLE COLUMN FOREIGN KEY
• COMPOSITE FOREIGN KEY

SINGLE COLUMN FOREIGN KEY:

In this type of constraint the foreign key is defined on a single column.
We can define this type of constraint both at column level and table level.

COLUMN LEVEL DEFINITION:

CREATE TABLE DEPT_FK_TAB
(DEPARTMENT_ID   NUMBER(4) CONSTRAINT DEPT_PK PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR2(30),
 MANAGER_ID      NUMBER(6),
 LOCATION_ID     NUMBER(4)
);

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4) CONSTRAINT EMP_FK REFERENCES DEPT_FK_TAB(DEPARTMENT_ID) 
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME,
       POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_FK_TAB';

FOREIGN KEY Constraint in Oracle : output

TABLE LEVEL DEFINITION:

DROP TABLE EMP_FK_TAB;

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID) 
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME,
       POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_FK_TAB';

COMPOSITE PRIMARY KEY CONSTRAINT:

DROP TABLE DEPT_FK_TAB;

DROP TABLE EMP_FK_TAB;

CREATE TABLE DEPT_FK_TAB
(DEPARTMENT_ID   NUMBER(4),
 DEPARTMENT_NAME VARCHAR2(30),
 MANAGER_ID      NUMBER(6),
 LOCATION_ID     NUMBER(4),
 CONSTRAINT DEPT_PK PRIMARY KEY(DEPARTMENT_ID, MANAGER_ID)
);

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID, MANAGER_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID, MANAGER_ID) 
);

To make a foreign key as composite key in child table, we need to make the combination of columns it references as the primary key of parent table.
In the above example, we can see that constraint EMP_FK of EMP_FK_TAB is a composite key and it consists of DEPARTMENT_ID and MANAGER_ID. It references the same combination of columns in DEPT_FK_TAB table. To enable this reference we had to make DEPARTMENT_ID and MANAGER_ID as composite primary key of DEPT_FK_TAB table.

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME,
       POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_FK_TAB';

Types of FOREIGN KEY constraint definition:

• In-line definition
• Out-of-line definition

In-line definition:

It states that FOREIGN KEY constraint is defined during table creation, either at column level or at table level.

SINGLE COLUMN FOREIGN KEY DEFINITION:

CREATE TABLE DEPT_FK_TAB
(DEPARTMENT_ID   NUMBER(4) CONSTRAINT DEPT_PK PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR2(30),
 MANAGER_ID      NUMBER(6),
 LOCATION_ID     NUMBER(4)
);

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4) CONSTRAINT EMP_FK REFERENCES DEPT_FK_TAB(DEPARTMENT_ID) 
);

COMPOSITE PRIMARY KEY DEFINITION:

CREATE TABLE DEPT_FK_TAB
(DEPARTMENT_ID   NUMBER(4),
 DEPARTMENT_NAME VARCHAR2(30),
 MANAGER_ID      NUMBER(6),
 LOCATION_ID     NUMBER(4),
 CONSTRAINT DEPT_PK PRIMARY KEY(DEPARTMENT_ID, MANAGER_ID)
);

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID, MANAGER_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID, MANAGER_ID) 
);

Out-of-line definition:

It states that FOREIGN KEY constraint is defined with ALTER TABLE statement.

SINGLE COLUMN FOREIGN KEY DEFINITION:

DROP TABLE EMP_FK_TAB;

DROP TABLE DEPT_FK_TAB;

CREATE TABLE DEPT_FK_TAB
(DEPARTMENT_ID   NUMBER(4)  CONSTRAINT DEPT_PK PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR2(30),
 MANAGER_ID      NUMBER(6),
 LOCATION_ID     NUMBER(4)
);

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4)
);

USING MODIFY STATEMENT:

ALTER TABLE EMP_FK_TAB MODIFY(DEPARTMENT_ID NUMBER(4) CONSTRAINT EMP_FK REFERENCES DEPT_FK_TAB(DEPARTMENT_ID));

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

USING ADD CONSTRAINT STATEMENT:

ALTER TABLE EMP_FK_TAB DROP CONSTRAINT EMP_FK;

ALTER TABLE EMP_FK_TAB ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

Disabling FOREIGN KEY Constraint:

ALTER TABLE EMP_FK_TAB DISABLE CONSTRAINT EMP_FK;

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

Enabling FOREIGN KEY Constraint:

ALTER TABLE EMP_FK_TAB ENABLE CONSTRAINT EMP_FK;

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

Dropping FOREIGN KEY Constraint:

ALTER TABLE EMP_FK_TAB DROP CONSTRAINT EMP_FK;

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       R_OWNER, 
       R_CONSTRAINT_NAME,
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_FK_TAB';

No row for foreign key constraint in the above output.

Impact of FOREIGN KEY Constraint on DML operations:

SINGLE COLUMN FOREIGN KEY:

INSERT:

ALTER TABLE EMP_FK_TAB ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID);

Inserting data into parent table DEPT_FK_TAB:

INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (10,'Administration',200,1700);
INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (20,'Marketing',201,1800);
INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (30,'Purchasing',114,1700);

SELECT * FROM DEPT_FK_TAB;

Inserting data into child table:

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (201,'Michael','Hartstein','MK_MAN',13000,100,20);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',6000,201,20);

SELECT * FROM EMP_FK_TAB;

If we try to enter data into EMP_FK_TAB table where DEPARTMENT_ID is other than 10, 20 or 30 then oracle will restrict us to do so. Because there will not matching key in DEPT_FK_TAB.

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (200,'Jennifer','Whalen','AD_ASST',4400,101,40);

UPDATE:

If we try to update any value in foreign key column which is not available in parent table then oracle will restrict us to do so.

UPDATE EMP_FK_TAB
SET DEPARTMENT_ID = 40
WHERE EMPLOYEE_ID = 201;

Oracle accepts null value in foreign key column.

UPDATE EMP_FK_TAB
SET DEPARTMENT_ID = NULL
WHERE EMPLOYEE_ID = 201;

SELECT * FROM EMP_FK_TAB;

DELETE:

We cannot delete any record in parent table where there is matching data in child table.

SELECT * FROM DEPT_FK_TAB;

SELECT * FROM EMP_FK_TAB;

DELETE DEPT_FK_TAB
WHERE DEPARTMENT_ID = 20;

In the above example we have data in EMP_FK_TAB for DEPARTMENT_ID 20. So oracle will not allow us to delete record for DEPATMENT_ID 20 from DEPT_FK_TAB.

COMPOSITE FOREIGN KEY:

INSERT:

DELETE EMP_FK_TAB;

ALTER TABLE EMP_FK_TAB DROP CONSTRAINT EMP_FK;

ALTER TABLE DEPT_FK_TAB DROP CONSTRAINT DEPT_PK;

ALTER TABLE DEPT_FK_TAB ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPARTMENT_ID, MANAGER_ID);

ALTER TABLE EMP_FK_TAB ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID, MANAGER_ID) 
REFERENCES DEPT_FK_TAB(DEPARTMENT_ID, MANAGER_ID);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (201,'Michael','Hartstein','MK_MAN',13000,100,20);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',6000,201,20);

In the above example, we can see that first insert statement has been failed. Reason is MANAGER_ID is passed as 100 which is not present in parent table DEPT_FK_TAB.

SELECT * FROM EMP_FK_TAB;

UPDATE:

If we try to update either MANAGER_ID or DEPARTMENT_ID column of EMP_FK_TAB with some value which is not present in DEPT_FK_TAB then oracle will throw error.

UPDATE EMP_FK_TAB
SET MANAGER_ID = 100
WHERE EMPLOYEE_ID = 202;

However, null values are allowed in foreign key column.

UPDATE EMP_FK_TAB
SET MANAGER_ID = NULL,
    DEPARTMENT_ID = NULL
WHERE EMPLOYEE_ID = 202;

DELETE:

DELETE EMP_FK_TAB;

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',6000,201,20);

SELECT * FROM EMP_FK_TAB;

We cannot delete any record in parent table where there is matching data in child table.

DELETE DEPT_FK_TAB
WHERE DEPARTMENT_ID = 20;

ON DELETE CASCADE Clause in Foreign Key:

ON DELETE CASCADE clause allows us to delete dependent child record when we delete any parent record.

DROP TABLE EMP_FK_TAB;

CREATE TABLE DEPT_FK_TAB
(DEPARTMENT_ID   NUMBER(4) CONSTRAINT DEPT_PK PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR2(30),
 MANAGER_ID      NUMBER(6),
 LOCATION_ID     NUMBER(4)
);

CREATE TABLE EMP_FK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID) ON DELETE CASCADE 
);

INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (10,'Administration',200,1700);
INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (20,'Marketing',201,1800);
INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (30,'Purchasing',114,1700);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (201,'Michael','Hartstein','MK_MAN',13000,201,20);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',6000,201,20);

SELECT * FROM DEPT_FK_TAB;

SELECT * FROM EMP_FK_TAB;

DELETE DEPT_FK_TAB
WHERE DEPARTMENT_ID = 20;

SELECT * FROM DEPT_FK_TAB;

SELECT * FROM EMP_FK_TAB;

We can see that both the rows from EMP_FK_TAB has been deleted on deletion of DEPARTMENT_ID 20 from DEPT_FK_TAB. Its due to ON DELETE CASCADE clause.

ON DELETE SET NULL Clause in Foreign Key:

ON DELETE SET NULL clause allows us to update foreign key column of dependent child record as null when we delete any parent record.

ALTER TABLE EMP_FK_TAB ENABLE CONSTRAINT EMP_FK;

ALTER TABLE EMP_FK_TAB ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPT_FK_TAB(DEPARTMENT_ID) 
ON DELETE SET NULL;

INSERT INTO DEPT_FK_TAB (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (20,'Marketing',201,1800);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (201,'Michael','Hartstein','MK_MAN',13000,201,20);

INSERT INTO EMP_FK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',6000,201,20);

SELECT * FROM DEPT_FK_TAB;

SELECT * FROM EMP_FK_TAB;

DELETE DEPT_FK_TAB
WHERE DEPARTMENT_ID = 20;

SELECT * FROM DEPT_FK_TAB;

SELECT * FROM EMP_FK_TAB;

DEPARTMENT_ID of EMP_FK_TAB has been updated with NULL on deletion of DEPARTMENT_ID 20 of DEPT_FK_TAB.

Impact of FOREIGN KEY Constraint on DDL operations:

We can not drop any parent table if there is any child table exists.

DROP TABLE DEPT_FK_TAB;

We need to either disable or drop the foreign key constraint or drop the child table to drop the parent table.

ALTER TABLE EMP_FK_TAB DROP CONSTRAINT EMP_FK;

DROP TABLE DEPT_FK_TAB;

RELATED TOPICS:

• NOT NULL Constraint in Oracle
• UNIQUE Constraint in Oracle
• PRIMARY KEY Constraint in Oracle