Foreign key in DBMS

Foreign key in DBMS: The Foreign key is a field or the set of fields in the relational database table, which points to the existing field in another table. It is a key that creates a relationship between the two tables. The main goal is to maintain the integrity of the relational data in the database relations. The foreign key acts as a cross-reference between the database tables because it references another table's primary key in the same database.

We are describing the foreign key concept with the following example, so you can easily understand it.

This example contains two tables. The name of the first table is Teacher_Details, and the name of the second table is Course_Assign.

The first table contains three columns. The name of these columns is Teacher_ID, Teacher_Name, and Teacher_Age. In this table, Teacher_Id acts as a primary key.

The second table also contains the three columns. And, the name of three columns in the second table is Course_ID, Course_name, and Teacher_ID, where Teacher_ID is selected as a foreign key, which points to the primary key in the Teacher table. And, the Course_ID acts as a primary in the Course table. 

First Table: Teacher

Teacher_ID (Primary Key)	Teacher_Name	Teacher_Age
201	Anuj	22
202	Anik	24
203	Manoj	23
204	Anuj	24

Second Table: Course

Course_ID (Primary key)	Course_Name	Teacher_ID (Foreign Key)
401	Math	201
402	C	202
403	Java	201
404	DBMS	203
405	Cloud Computing	204
406	Big Data	202

Create the Foreign Key in RDBMS

We have learned the meaning of foreign key with its example. Now, we will discuss how to create a foreign key in the SQL table. So, we will take the above table to create the foreign key. In SQL, we can create the foreign key as shown below:

CREATE TABLE Course (
    Course_ID int NOT NULL PRIMARY KEY,
    Course_Name varchar NOT NULL,
    Teacher_ID int FOREIGN KEY REFERENCES Teacher(Teacher_ID)
);

Using the following query, we can also create the foreign key constraint on the Teacher_ID column in the Course table when the table is already created.

ALTER TABLE Course
ADD FOREIGN KEY (Teacher_ID) REFERENCES Teacher(Teacher_ID);