Understand The Role of Foreign Key in DBMS With Example

Summary: You will learn in this article the concept of Foreign Key in DBMS with the example. This Foreign Key in DBMS plays the important role in the database management systems. let’s Understand The Role of Foreign Key in DBMS With Example. I hope you will enjoy it!

What is Foreign Key in DBMS

A foreign key is totally different from a super key, candidate key, or primary key because an FK-foreign key is the one that’s used to link or connect two tables together or create connectivity between the two.

Foreign keys put the “relational” in “relational database” – they help define the relationship between the tables. They allow maintaining referential integrity across their database. Furthermore, the Foreign keys (FK) also facilitate or help end-users by preventing errors and improving the performance of any operation that’s pulling data/information from tables linked by indexed foreign keys.

Here, in this article, we will also discuss the use of foreign keys, and look at some examples that will help us to understand the concept of working. We will also see its practical implementation on a database, such as How to create and delete a foreign key on a table.

What is a Foreign Key

A foreign key is used for the purpose of linking two tables together via the primary key. Therefore, it implies that the columns of one table point to the primary key attribute of the other table.
It more implies that if any attribute is set as a primary key (PK) attribute will work in another table as a foreign key attribute. however, one should know that an FK-foreign key has nothing to do with the primary key.
A foreign key (FK) is a column or group of columns in a relational database table that provides a connection or link between data in two tables.
It is a column/columns that reference a column (most often the primary key) of another table.

SQL FOREIGN KEY Constraint

The FK-FOREIGN KEY constraint is used for the purpose of preventing actions that would destroy links between tables.
A FOREIGN KEY is a field or collection of fields in one table, that refers to the PK-PRIMARY KEY in another table.
The table with the foreign key (FK) is known as the child table, and the table with the primary key is known as the referenced or parent table.

Use of Foreign Key in DBMS

The main use of a foreign key is simply to link or connection the attributes of two tables together with the help of a primary key attribute. Furthermore, it is used generally to create and maintain the relationship between the two relations.

Foreign Key in DBMS With Example

Let’s discuss an example to understand the concept of working with a foreign key.
First of all, we consider two tables Customers and Orders having their respective attributes as shown in the given table structure. There are dummy data or information in both tables for understanding the Example of Foreign Key:
Example of foreign key in dbms
In the above tables, you can see that one attribute, is common in both tables, that is CustomerID, but one thing is that it has different key constraints for both tables. In the Customers table, the field CustomerID is a primary key because it is uniquely identifying all other fields of the Customers table. On the other hand, as you can see that CustomerID is a foreign key attribute for the Orders table because it is acting as a primary key (PK) attribute for the Customers table.
Therefore, it means that both the Customers and Orders tables are linked with one another because of the CustomerID attribute.
In the below-shown image, you can view the following structure of the relationship between the two tables.
Relationship between two table: foreign key

1. Creating Foreign Key constraint

On CREATE TABLE
The given below syntax will make us learn How to create a foreign key in a table:

MySQL:

CREATE TABLE ORDERS (  
    ProductName varchar (100) NOT NULL,  
    CustomerID int,  
    FOREIGN KEY (CustomerID) REFERENCES CUSTOMERS (CustomerID)  
);

CREATE TABLE ORDERS (

ProductName varchar (100) NOT NULL,

CustomerID int,

FOREIGN KEY (CustomerID) REFERENCES CUSTOMERS (CustomerID)

);

So, during this method, we can set an FK-foreign key for a table in the MYSQL database.
In the case of creating a foreign key for a table in SQL or Oracle server, the following syntax will work:

SQL or Oracle server

CREATE TABLE ORDERS (  
    ProductName varchar (100) NOT NULL,  
    CustomerID int FOREIGN KEY REFERENCES CUSTOMERS (CustomerID)  
);

CREATE TABLE ORDERS (

ProductName varchar (100) NOT NULL,

CustomerID int FOREIGN KEY REFERENCES CUSTOMERS (CustomerID)

);

2. On ALTER TABLE

The given below the following syntax is used for creating a foreign key constraint on ALTER TABLE:

ALTER TABLE ORDERS  
ADD FOREIGN KEY (CustomerID) REFERENCES CUSTOMERS (CustomerID);

1 2	ALTER TABLE ORDERS ADD FOREIGN KEY (CustomerID) REFERENCES CUSTOMERS (CustomerID);

3. Dropping Foreign Key

Furthermore, in order to delete a foreign key (FK), the given below-described syntax that can be used:

ALTER TABLE ORDERS  
DROP FOREIGN KEY FK_CUSTOMERSORDERS;

1 2	ALTER TABLE ORDERS DROP FOREIGN KEY FK_CUSTOMERSORDERS;

So, during this method, we can drop a foreign key using the ALTER TABLE in the MYSQL database.

Important Things To Note in Foreign Key in DBMS
When you want to drop the FK-foreign key, one needs to take care of the integrity of the tables which are connected or linked via a foreign key.
Therefore, in case you make changes in one table and disturb the integrity of both tables, then it may display certain errors due to improper connectivity between the two tables.

Some referential actions associated with foreign key activities include the following:

1) Cascade

When rows in the parent table are deleted, then the matching FK-foreign key columns in the child table are also deleted, creating a cascading delete.

2) Set Null

When a referenced row in the parent table is deleted or updated, the FK-foreign key values within the referencing row are set to null to keep up referential integrity.

3) Triggers

Referential actions are normally implemented as triggers. In many ways, FK-foreign key actions are identical to user-defined triggers. Furthermore, To ensure proper execution, ordered referential actions are sometimes replaced with their equivalent user-defined triggers

4) Set Default

This referential action is identical to "set null". Therefore, the FK-foreign key values in the child table are set to the default column value when the referenced row in the parent table is deleted or updated.

5) Restrict

This is the general referential action related to a foreign key. Furthermore, a value in the parent table can’t be deleted or updated as long as it is noted by a foreign key in another table.

6) No Action

This referential action is identical in function to the "restrict" action except that a no-action check is performed only after trying to alter the table.

The Difference Between Primary Key and Foreign Key

There is the difference between Primary key and foreign key:

PRIMARY KEY	FOREIGN KEY
A PK-primary key is used for the purpose of ensuring data in the specific column is unique.	An FK-foreign key is a column or group of columns in a relational database table that provides a connection or link between data in two tables.
PK uniquely identifies a record in the relational database table.	FK refers to the field in a table that is the primary key of another table.
Only one PK-primary key is allowed in a table.	Whereas more than one FK-foreign key is allowed in a table.
This is a combination of UNIQUE and NOT Null constraints.	It can contain any duplicate values & a table in a relational database.
It doesn’t allow NULL values.	They can also contain NULL values.
Its value can’t be deleted from the parent table.	Whereas, its value can be deleted from the child table.
PK constraint can be implicitly defined on the temporary tables.	Whereas, it constraint can’t be defined on the local or global temporary tables.

Important Things To Note in Foreign Key in DBMS
Practically we can say that an FK-foreign key has nothing to do with the PK-primary key tag of another table, Therefore, if it points to a unique column of another table then too, it would be an FK. Thus, a correct definition of the foreign key would be: FK-Foreign keys are the columns of a table that points to the candidate key of another table.