Foreign Key in DBMS

In DBMS, there are different types of keys available that are used for various purposes. Foreign Key is a column that refers to the primary key/unique key of another table. So it demonstrates the relationship between tables and acts as the cross reference among them.

Foreign Key

Foreign keys are a set of constraints in DBMS that establish relationships between tables and also ensure consistency and integrity of data. A foreign key is applied to a column of one table which references the primary key of a column in another table.

Note that it is mandatory that the other column must have a primary key as it references the data that is related in different tables and creates a relational structure. Foreign key enforces referential integrity and makes sure that data is referenced from one table to table. In order to create a Foreign key we will specify the relationship between the columns during the creation of the table’s structure. We add the keyword REFERENCES in order to specify that this particular column will refer to another column of another table.

Also, note that it is possible for a foreign key of a column to reference a column in the table itself. For this to happen the referenced column should be a primary key and this is called Self-Reference.

In this article, we will thoroughly implement foreign keys in DBMS with the help of MYSQL as we perform all the operations in a table that is inside a database.

Syntax For Creating and Deleting Foreign Key

Let’s see the Foreign Key syntax used for creating a table.

Syntax for Creating Foreign Key:

CREATE TABLE Child_table_name (

Child_Column1 data_type,

Child_Column2 data_type, …,

FOREIGN KEY (Child_foreign_key_column)

REFERENCES referenced_table_name (referenced_primary_key_column) );

Below syntax is used to delete the foreign key in DBMS.

Syntax for Dropping Foreign Key:

ALTER TABLE Child_table_name

DROP FOREIGN KEY Child_foreign_key_name;

Need of Foreign Keys in DBMS

Foreign keys plays a major role in database management systems (DBMS) for the following reasons:

• Data Integrity: We need foreign keys as they help us making sure that data is consistent, complete, between both the tables and overall accuracy is maintained.
• Query Optimization: Foreign keys optimizes the query execution by utilizing query plans more efficiently and improving the relationships between tables. It also helps in fast data retrieval.
• Establishing Relationships: The main requirement of foreign keys is the establishment of relationships between tables. It makes sure that data is linked across multiple tables and helps in storing and retrieving data.
• Data Security: Foreign keys helps in improving the security of data by preventing unauthorized modifications or deletions of important data in the referenced table.
• Database Maintenance: Foreign keys are required in database maintenance tasks and help to ensure integrity and consistency of data during these operations.

Implementing Foreign Key

To implement foreign keys in a column of a table, follow the below mentioned steps and make sure to have MYSQL workbench or MYSQL command line client installed in your systems.

Step 1: To get started with foreign keys we need to create a new database first and inside the database we will create two tables and link them. We use the CREATE DATABASE command to create a new database.

Query:

CREATE DATABASE GEEKSFORGEEKS;

Step 2: Now we will Use or Select the database after creating it. We use the USE command in order to select our database.

Query:

USE GEEKSFORGEEKS;

Selecting database

Step 3: As we have selected our database now we will create a table in our database. To create a table we use CREATE TABLE command. As an example we are creating a table Courses and Students and then we will link them with foreign key. Then we will look at the structure of the tables also.

Query:

 CREATE TABLE STUDENT(
     STUDENT_ID INT PRIMARY KEY,
     NAME VARCHAR(20),
     ADDRESS VARCHAR(20),
     AGE INT,
     DOB DATE);

DESC STUDENTS;

Output:

Creating table Student

 CREATE TABLE COURSES(
     COURSE_NAME VARCHAR(20),
     INSTRUCTOR VARCHAR(20),
     REFERENCE_ID INT,
     CONSTRAINT FK_REFER FOREIGN KEY (REFERENCE_ID)
     REFERENCES STUDENT(STUDENT_ID));

DESC COURSES;

Output:

Creating courses table

We have successfully implemented Foreign Key in our tables. We have referenced the REFERENCE_ID column of table COURSES with the STUDENT_ID column of the table STUDENT and as we have learnt that Foreign Keys are used for referential integration of two columns.

Conclusion

In this article we have learned about foreign key in DBMS. To summarize the article, a foreign key establishes relationship between tables and ensures consistency and integrity of data . It is applied to a column of one table which references the primary key of a column in another table. It is mandatory that the other column must have a primary key as it references the data which is related in different tables and creates a relational structure. Foreign key enforces referential integrity and makes sure that data is referenced from one table to table. In order to create a Foreign key we will specify the relationship between the columns during the creation of table’s structure .

FAQs on Foreign Key in DBMS

Q.1: What is main difference between foreign key and primary key?

Answer:

A primary key is used to ensure data in the specific column is unique. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

Q.2: Is it necessary that the referenced column should have a primary key ?

Answer:

It is mandatory that the other column must have a primary key as it references the data which is related in different tables and creates a relational structure.

Q.3: Can a foreign key reference a column in own table?

Answer:

Yes, it is possible for a foreign key to reference a column in its own table and it is called as Self-Reference.