SQL FOREIGN KEY

What is FOREIGN KEY in SQL?

To create a relationship between two tables, we primarily need two keys, one is the primary key, and the other is the foreign key.

A primary key is a column or a group of columns that uniquely identifies a particular row in a table.

For example, the University roll number identifies each student's data uniquely. The primary key values cannot be null or have duplicates, and one table can have only one primary key. However, this one primary key in a table can consist of single or multiple columns.

A foreign key is a column or a group of columns in a table whose values are referenced from a primary key in another table. A primary key in SQL uniquely identifies records in a table. However, you must have a primary key for using a foreign key in SQL.

The table containing the primary key is known as the parent table, whereas the table which contains the foreign key is known as the child table. Therefore, a foreign key relates two tables in a database. Moreover, the primary key values in the parent table must match the values of the foreign key in another table.

Let's say we have a table named student which contains the Names, Roll Numbers, and Percentages of the students in a University.

The table is shown below-

The Roll No column acts as a Primary key in the student table. It uniquely identifies a particular row in the table. We have another table named Course which contains the Roll No and the Name of the Course in which a particular student is enrolled.

The Course table is as shown below-

However, the Roll No column in the Course table is used as a foreign key that references the student table's primary key. Therefore, the Roll No column acts as a foreign key.

We apply foreign key constraints in SQL that are used to prevent actions that would destroy the links between the tables. These constraints do not even allow entering invalid data into a table.

Properties of FOREIGN KEY in SQL

• The foreign key values in a table can be null.
• The foreign key may contain duplicate values.
• A table may have more than one foreign key.
• The parent table records can be deleted if no child table record exists.
• Parent Table cannot be updated if its child table exists.

Rules for Creating FOREIGN KEY in SQL

• The foreign key columns in the child table and the primary key columns in the parent table must contain the same types of data.

  For Example: If we have two tables courses and student and the courseId field is common in both the tables. Then, this field will act as a primary key in the course table whereas a foreign key in the student table. Since this field will contain integer values, therefore while creating this field, both the tables should define it with int type only.

• Both the referencing and the referenced tables should be in the same database, which means that if the tables courses and student are being linked together using foreign and primary keys, then both these should be present in the same database.

• The referenced table must contain unique values as they are used to uniquely identify a row in a table.

  For example, let's say we have a table student which contains studentID as the primary key. Therefore, this column should contain unique values so that each student can be identified individually.

• A single column in a table may contain multiple foreign key constraints.

• A foreign key column can be a stored computed column but not a virtual computed column.

FOREIGN KEY on CREATE TABLE

The CREATE command creates a new table in a database. However, it is also used to create a Foreign Key in a new table. Let us see an example to define the foreign key using the create command.

We have a table Department which contains the information about a department and has DeptNo, DName, and Location as its columns.

To create this table, we will be executing the command -

The above query will create a table with DeptNo as the primary key, which will act as a reference table or a parent table for another table that will contain the foreign key.

Let us create another table Employee, which will be the child table for the Department table. It contains the EmpNo, EmpName, Salary, and DeptNo as its fields.

To create this table, you have to execute the following command.

Therefore, after executing the above query, DeptNo in the Employee table will act as a foreign key in this new table. Using the above query, we have linked these two tables based on a common column DeptNo.

FOREIGN KEY on ALTER TABLE

The ALTER table command in SQL is used to alter the columns in an already existing table. However, if you have already created a table and now you want to add a foreign key constraint to it, then you can execute the following query for doing so.

We have assumed that the Employee and the Department tables have already been created, and DeptNo is acting as a common column between the two tables. The above query adds a foreign key constraint on the table Employee if it has already been created.

DROP a FOREIGN KEY Constraint

Once you have created a foreign key constraint, you may sometime wish to drop it from the table. The DROP command is used to delete a table from a database. However, it can also be used to drop a foreign key constraint in a table.

To do so, you have to execute the following query.

In the query, you need to provide the name of the table and the name of the foreign key which you want to delete. As a result of the above query, the DeptNo column from the Employee table will be deleted.

FOREIGN KEY at Column Level

You can use the foreign key at the column level in which you can apply a foreign key constraint in a table without using the foreign key keyword.

We are using the Department and the Employee tables to show the relationship between the two tables at the column level.

You can execute the following query to use the foreign key at the column level.

Or

The Department table should have the primary key of type int for the above queries. If the Department table has only one primary key, then the column name in the syntax can be omitted as specified in the first query. However, both queries can be used to create a foreign key at the column level.

FOREIGN KEY with ON DELETE SET NULL

Using this ON DELETE SET NULL keyword, if the parent table records are deleted, then the corresponding records in the child table in the foreign key column will be set to null. However, the records in the child table will not be deleted but will only be set to null.

You should execute the following query to use the ON DELETE SET NULL in SQL.

After executing the above query, the DeptNo column values in the Employee table will be set to null. However, the records of the Employee table will not be deleted.