Foreign Key in DBMS and Best Practices

A foreign key in DBMS is a very important part that ensures data consistency and referential integrity in the database. A foreign key is being used to establish a relationship between two tables in an RDBMS. In this article post, you will learn the complete application of foreign keys in DBMS to maintain the integrity and accuracy of the data.

What is a Foreign Key in DBMS?

A foreign key is a set of rules that are used to ensure the consistency and accuracy of data. It is being used to establish relationship links between two tables and enforces the referential integrity between both tables. A foreign key ensures that data in one table is also available with its corresponding data in another table.

In another word, we can say that the foreign key acts like a bridge between two tables and create a link in the data from one table to another table. Foreign key ensures that the data in the related tables remain consistent and accurate.

Example: We have taken two tables – “Students” and “Classes” to implement the foreign key in DBMS. The ‘StudentID’ column is a primary key and the ‘ClassID’ column is a foreign key of the ‘Students’ table that references the ‘ClassID’ column in the ‘Classes’ table. For example, the ‘Students’ table contains the following data –

StudentID	FirstName	LastName	ClassID
1	John	Doe	1
2	Jane	Doe	2
3	Bob	Smith	1

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type PRIMARY KEY,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_NameN Data_Type FOREIGN KEY REFERENCES Table_NameN(Column_Name));

Example:

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT FOREIGN KEY REFERENCES Classes(ClassID));

And the “Classes” table contains the following data-

ClassID	ClassName
1	Mathematics
2	Science
3	MBA

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type PRIMARY KEY,
Column_Name2 Data_Type);

Example:

CREATE TABLE Classes (
ClassID INT PRIMARY KEY,
ClassName VARCHAR(30) NOT NULL);

In this scenario, the “ClassID” column in the “Students” table is a foreign key that references the “ClassID” column in the “Classes” table. The foreign key ensures that the “ClassID” values in the “Students” table correspond to the “ClassID” values in the “Classes” table. The “Classes” table is the parent table because it is being referenced by the “Students” table and the “Students” table is the child table.

Foreign Key Basic Properties

Refer to the below basic properties of a foreign key –

1. Parent tables data can not be modified or deleted If the related data is available in the child table.
2. A table in DBMS can hold one or more than one foreign key.
3. A foreign key can hold duplicate values in the table.
4. The values of the foreign key might be NULL in the table.

How Does a Foreign Key Work in DBMS?

A foreign key established a relationship between two tables. The foreign key in the referencing table (in this case, the “Students” table) is linked to the primary key in the referenced table (in this case, the “Classes” table). The primary key in the referenced table is used as the unique identifier for the data in that table.

When a new record is added to the referencing table(Child table), the foreign key value must correspond to an existing value in the referenced table (Parent table). This ensures that the data in the referencing table(Child table) is consistent with the data in the referenced table (Parent table). If the foreign key value does not match any existing value in the referenced table, an error will be generated, and the record will not be added to the referencing table.

Why are Foreign Key Constraints Important?

Refer to the below importance of foreign key constraints –

1. Foreign key constraints are important because they help to maintain data consistency and integrity in a database.
2. It can be implemented to create a relationship between two tables using a foreign key and ensure that the data entered into the child table corresponds to the data in the parent table.
3. A foreign key helps to prevent data inconsistencies that can lead to errors and other data issues in the RDBMS.
4. Foreign keys can be used to cascade while updating or deleting data across the related tables.

Best Practices for Using Foreign Keys in DBMS

Refer to the below best practices for using foreign keys in DBMS –

1. A foreign key of a child table must be linked to a primary key of a parent table.
2. A primary key in the parent table must b unique.
3. The foreign key and primary key must have the same data type.
4. Keep the parent table (Referenced) up-to-date to ensure that the data in the child table (Referencing) remains consistent.
5. When the parent table(Referenced) is changed then the child table (Referencing) should be updated accordingly to maintain the integrity of the data.

Applications of Foreign Key in DBMS

You will learn here the application of foreign key in DBMS as follows –

1. Foreign Key Creation While Table Creation

You will learn here to create a foreign key while creating a new table.

A. Foreign Key Creation on a Column Without Providing Its Name While Table Creation at Column Level

In this example, we will create a table named ‘Students1’ and assign a foreign key without providing its name with the ‘ClassID’ column that will be referenced to ‘ClassID’ column of the earlier created table named ‘Classes’. In this scenario, DBMS will provide a system-generated default name for this foreign key which we will see ahead. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type PRIMARY KEY,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_NameN Data_Type FOREIGN KEY REFERENCES Table_NameN(Column_Name));

Example:

CREATE TABLE Students1 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT FOREIGN KEY REFERENCES Classes(ClassID));

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this foreign key. In this scenario, DBMS has provided a system-generated default name for this foreign key (FK__Students1__Class__1AD3FDA4), which can be seen in the above screenshot marked with yellow color on the left side.

B. Foreign Key Creation on a Column Without Providing Its Name While Table Creation at Table Level

In this example, we will create a table named ‘Students2’ and follow the same above process but we will place a Foreign Key keyword at a different place in the query. Please refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type PRIMARY KEY,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_NameN Data_Type,
FOREIGN KEY (Column_NameN) REFERENCES Table_NameN(Column_Name));

Example:

CREATE TABLE Students2 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
FOREIGN KEY (ClassID) REFERENCES Classes(ClassID));

Screenshot: Below screenshot for reference –

In the above query example, we have not provided any specific name for this Foreign key. In this scenario, DBMS has provided a system-generated default name for this foreign key (FK__Students2__Class__1DB06A4F), which can be seen in the above screenshot marked with yellow color on the left side.

C. Foreign Key Creation on a Column With Providing Its Name While Table Creation at Column Level

In this example, we will create a table named ‘Students3’ and assign a foreign key named ‘FK_Students3_ClassID’ with the ‘ClassID’ column. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type PRIMARY KEY,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_NameN Data_Type CONSTRAINT Constraint_Name FOREIGN KEY REFERENCES Table_NameN(Column_Name));

Example:

CREATE TABLE Students3 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT CONSTRAINT FK_Students3_ClassID FOREIGN KEY REFERENCES Classes(ClassID));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the FK name ‘FK_Students3_ClassID’, which can be seen in the above screenshot marked with yellow color on the left side.

D. Foreign Key Creation on a Column With Providing Its Name While Table Creation at Table Level

In this example, we will follow the same above process but we will place a Foreign Key keyword at a different place in the query. Please refer to the below syntax –

Syntax:

CREATE TABLE Table_Name (
Column_Name1 Data_Type PRIMARY KEY,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_NameN Data_Type,
CONSTRAINT Constraint_Name FOREIGN KEY (Column_NameN) REFERENCES Table_NameN(Column_Name));

Example:

CREATE TABLE Students4 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students4_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the FK name ‘FK_Students4_ClassID’ for this foreign key, which can be seen in the above screenshot marked with yellow color on the left side.

2. Foreign Key Creation After Table Creation

You will learn here to create a foreign key after creating a new table.

A. Foreign Key Creation on a Column Without Providing Its Name after Table Creation

In this example, We will create a foreign key without providing a name with the ‘ClassID’ column of an existing table named ‘Students5’. In this scenario, DBMS will provide a system-generated default name for this foreign key which we will see ahead. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD FOREIGN KEY (Column_Name) REFERENCES Table_NameN(Column_Name);

Example:

ALTER TABLE Students5
ADD FOREIGN KEY (ClassID) REFERENCES Classes(ClassID);

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this foreign key. In this scenario, DBMS has provided a system-generated default name for this foreign key (FK__Students5__Class__2645B050), which can be seen in the above screenshot marked with yellow color on the left side.

B. Foreign Key Creation on a Column With Providing Its Name after Table Creation

In this example, We will create a foreign key by providing the name ‘FK_Students6_ClassID’ with the ‘ClassID’ column of an existing table named ‘Students6’. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name FOREIGN KEY (Column_Name) REFERENCES Table_NameN(Column_Name);

Example:

ALTER TABLE Students6
ADD CONSTRAINT FK_Students6_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID);

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the FK name ‘FK_Students6_ClassID’ for this foreign key, which can be seen in the above screenshot marked with yellow color on the left side.

3. Delete or Drop a Foreign Key in DBMS

You will learn here to delete or drop an existing foreign key. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
DROP CONSTRAINT Constraint_Name;

Example:

ALTER TABLE Students5
DROP CONSTRAINT FK__Students5__Class__2645B050;

Screenshot: Below screenshot for reference –

You can see in the above query example we have deleted the foreign key ‘FK__Students5__Class__2645B050’, which can not be seen in the above screenshot marked with yellow color on the left side.

4. Disable a Foreign Key in DBMS

You will learn here to disable an existing foreign key. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
NOCHECK CONSTRAINT Constraint_Name;

Example:

ALTER TABLE Students6
NOCHECK CONSTRAINT FK_Students6_ClassID;

Screenshot: Below screenshot for reference –

You can see in the above query example we have disabled the foreign key ‘FK_Students6_ClassID’. Now the relationship between both tables is suspended till it is enabled.

5. Enable a Foreign Key in DBMS

You will learn here to enable an existing foreign key. Refer to the below syntax –

Syntax:

ALTER TABLE Students6
CHECK CONSTRAINT FK_Students6_ClassID;

Example:

ALTER TABLE Students6
CHECK CONSTRAINT FK_Students6_ClassID;

Screenshot: Below screenshot for reference –

You can see in the above query example we have enabled the foreign key ‘FK_Students6_ClassID’. Now the relationship between both tables is enabled and works.

6. To Know the Status of Foreign Key in DBMS

You will learn here to know the status of the foreign key in DBMS whether it is enabled or disabled. Refer to the below syntax –

Syntax:

SELECT Column_Name1, Column_NameN FROM sys.Constraint_Name

Example:

SELECT NAME, TYPE_DESC, is_disabled FROM sys.foreign_keys

Screenshot: Below screenshot for reference –

You can see in the above query example that all the foreign keys are showing in the result set where the is_disabled column is showing the status of foreign key constraints. Is_disabled field value 0 means the foreign key is enabled whereas value 1 means the foreign key is disabled.

7. To Check the List of Constraints in DBMS

You will learn here to do checklists of present constraints in DBMS. Refer to the below syntax –

Syntax:

SELECT * FROM sys.Objects_Name
WHERE TYPE IN (Constraint_Type1,Constraint_TypeN)
ORDER BY CREATE_DATE DESC;

Example:

SELECT * FROM sys.all_objects
WHERE TYPE IN ('F','PK')
ORDER BY CREATE_DATE DESC;

Screenshot: Below screenshot for reference –

You can see in the above query example all the primary key and foreign keys lists are showing.

Referential Actions on Parent Table

2 types of referential actions can be implemented while setup up foreign key constraints on the parent table as follows –

1. On Delete
2. On Update

Foreign key constraint allows implementing the referential actions when the row data of a parent table is being deleted or updated as follows –

1. No Action
2. Cascade
3. Set Null
4. Set Default

Syntax:

CREATE TABLE TableName (
Column_Name1 Data_Type,
Column_Name2 Data_Type,
Column_Name3 Data_Type,
Column_NameN Data_Type,
CONSTRAINT Constraint_Name FOREIGN KEY (Child_Table_Column_NameN) REFERENCES Parent_Table(Parent_Table_Column_Name)
ON UPDATE No action
ON DELETE No action);

Example:

CREATE TABLE Students7 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students7_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON UPDATE No action
ON DELETE No action);

Screenshot: Below screenshot for reference –

You can see in the above query example foreign key ‘FK_Students7_ClassID’ has been created with no action on delete and update. If the user tries to update or delete the data of the parent table the corresponding data is available in the child table then DBMS will throw an error and roll back the delete or update operation.

1. Delete Action on Parent Table

You can define the appropriate actions when a user deletes any record in the parent table that corresponding data is already available in the child table as follows –

a. ON DELETE NO ACTION

DBMS throws an error and roll-back the delete operation in the parent table under this action.

Example:

CREATE TABLE Students7 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students7_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON DELETE No action);

b. ON DELETE CASCADE

DBMS deletes the record in the child table that is about the record deleted from the parent table under this action.

Example:

CREATE TABLE Students8 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students8_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON DELETE CASCADE);

c. ON DELETE SET NULL

DBMS updates the column value as ‘NULL’ of the child table which is about the record deleted from the parent table. The foreign key must be nullable while using this action.

Example:

CREATE TABLE Students9 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students9_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON DELETE SET NULL);

d. ON DELETE SET DEFAULT

DBMS updates the column value as the default value that is about the parent table. The foreign key must have set a default value while using this action.

Example:

CREATE TABLE Students10 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students10_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON DELETE SET DEFAULT);

2. Update Action on Parent Table

You can define the appropriate actions when a user updates any record in the parent table that corresponding data is already available in the child table as follows –

a. ON UPDATE NO ACTION

DBMS throws an error and roll-back the update operation in the parent table under this action.

Example:

CREATE TABLE Students11 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students11_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON UPDATE NO ACTION);

b. ON UPDATE CASCADE

DBMS updates the record in the child table that is about the record updated from the parent table under this action.

Example:

CREATE TABLE Students12 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students12_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON UPDATE CASCADE);

c. ON UPDATE SET NULL

DBMS updates the column value as ‘NULL’ of the child table which is about the record updated from the parent table. The foreign key must be nullable while using this action.

Example:

CREATE TABLE Students13 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students13_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON UPDATE SET NULL);

d. ON UPDATE SET DEFAULT

DBMS updates the column value as the default value that is about the parent table. The foreign key must have set a default value while using this action.

Example:

CREATE TABLE Students14 (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
ClassID INT,
CONSTRAINT FK_Students14_ClassID FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
ON UPDATE SET DEFAULT);

Test Scenarios of Foreign Key Constraints

You will learn here all test scenarios of Foreign key constraint that it is working properly or not as follows –

A. Foreign Key Test with Insert Statement

If you remembered that we have created Classes parent table and child tables from Students1 to Students14 and implemented foreign key in all child tables, where we will take a table named ‘Students14’ for testing with insert statement that foreign keys are working properly or not. The parent table (Classes) does not have any records with it and we are trying to insert a few records into the child table (Students14) as follows –

Example:

INSERT INTO Students14 (StudentID, FirstName, LastName, ClassID) VALUES
('12','Mr','Batra',1),
('13','Mrs','Chaddha',1),
('14','Ms','Devi',1)

Screenshot: Below screenshot for reference –

You can see in the above query example foreign key ‘FK_Students14_ClassID’ has thrown an error i.e. ‘The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Students14_ClassID”. The conflict occurred in database “SQLPOST”, table “dbo. Classes”, column ‘ClassID’. The statement has been terminated.’. That means there is no corresponding data in the parent table and due to this, this data can not be inserted into the child table.

So, now we will insert one record into the parent table as well as a few records into the child table to check coming scenarios using the below statements –

Example:

INSERT INTO Classes (ClassID, ClassName) VALUES
(1,'BCA'),
(2,'MCA'),
(3,'MBA')

INSERT INTO Students8 (StudentID, FirstName, LastName, ClassID) VALUES
('12','Mr','Batra',1),
('13','Mrs','Chaddha',1),
('14','Ms','Devi',1),
('15','Mr','Jatav',2),
('16','Md','Zubair',3),
('17','Ms','Khanna',3)

In the above example, you will be able to insert records into the child table ‘Students8’ after inserting corresponding data into the parent table ‘Classes’. Now we can see the inserted data into both tables using the below query example –

Example:

select * from Students8;
select * from Classes;

Screenshot: Below screenshot for reference –

You can see in the above query example that all the inserted data is showing accordingly.

B. Foreign Key Test with Delete Statement

If you remembered that we have created child table ‘Students8’ with action on delete cascade, thus we will try to delete one of the data from parent table ‘Classes’ and let’s see below –

Example:

DELETE FROM Classes WHERE ClassID=3;

Screenshot: Below screenshot for reference –

You can see in the above query example that the parent table ‘Classes’ data which class id 3 is deleted successfully which means the data about class id 3 in the child table ‘Students8’ should be deleted automatically as the action was set as on delete cascade, let’s see using below query example –

Example:

select * from Students8;
select * from Classes;

Screenshot: Below screenshot for reference –

You can see in the above query example that the class id 3 data is deleted from both the tables (Parent and child tables) successfully.

C. Foreign Key Test with Update Statement

If you remembered that we have created child table ‘Students12’ with action on update cascade, thus we will try to update one of the data from parent table ‘Classes’ and let’s see below –

Example:

UPDATE Classes SET ClassName ='BBA' WHERE ClassID=3 and ClassName='MBA';

Screenshot: Below screenshot for reference –

You can see in the above query example that the parent table ‘Classes’ data which class id is 3 is updated with BBA instead of MBA successfully that means the data about class id 3 in the child table ‘Students12’ should be updated automatically as the action was set as on update cascade, let’s see using below query example –

Example:

SELECT * FROM Students12;
SELECT * FROM Classes;

Screenshot: Below screenshot for reference –

You can see in the above query example that the class id 3 data is updated from both the tables (Parent and child tables) successfully.

Note: We have tried to explain all possible scenarios about the foreign key in DBMS at a summary level, where you have to read the complete article and try to elaborate on all possible scenarios with you and give more time to practice it.

Difference between Primary Key and Foreign Key

Refer to the below differences between primary key and foreign key in DBMS –

1. A primary key is used to uniquely identify a record in a table, while a foreign key is used to establish a relationship between two tables.
2. Primary key is a unique identifier for a record in a column of a table but a foreign key is a column of a table that refers to the primary key column of another table.
3. The primary key ensures that the data of each row in the table is unique from other row’s data in the table itself but a foreign key establishes a link between two tables to enforce referential integrity that ensures data can not be deleted or updated from a parent(Referenced) table if it is still referenced by another table.
4. A table can hold only one primary key whereas a table can hold more than one foreign key.
5. A primary key is a combination of not-null and unique but a foreign key contains duplicate values.
6. Primary key does not allow null values whereas a foreign key allows null values in it.

FAQs

Conclusion

Foreign keys are a very important part of relational database management systems. It helps to maintain the data integrity and referential integrity in RDBMS. How to create and use foreign key constraints understanding is a very important skill for any database developer or administrator. We have tried to cover all aspects of Foreign Keys, including their definition, importance, and possible scenarios. We hope that this article has helped you in gaining a better understanding of Foreign Keys and you would have liked this very well.

Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

What is SQL Full Form and Its Key Feature?	What is RDBMS Full Form and Its Key Feature?
DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What is Database Language?
What are Integrity Constraints in DBMS?	Key Constraints in DBMS With Fundamentals
Primary Key Constraint in SQL	Unique Key in DBMS and Best Practices