SQL FOREIGN KEY

SQL FOREIGN KEY

A foreign key in sql 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. 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.

Foreign key example in sql

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 as 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.

SQL 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.

MySQL:

SQL Server / Oracle / MS Access:

To enable the naming of a FOREIGN KEY constraint and to define a FOREIGN KEY constraint across multiple columns, utilize the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

SQL 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.

MySQL / SQL Server / Oracle / MS Access:

To enable the naming of a FOREIGN KEY constraint and to define a FOREIGN KEY constraint across multiple columns, utilize the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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.

MySQL:

SQL Server / Oracle / MS Access:

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.

SQL 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.

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

MYSQL:

Primary key vs Foreign key in SQL