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);