What is Foreign Key in SQL

What is a Foreign Key in SQL?

A foreign key in SQL is a constraint in the database that binds two tables. It can be simply understood as a column (or a combination of columns) in one table whose values must match those of another table’s column. The Foreign key constraint in the database imposes a referential integrity on the table, which means that if column A corresponds to column B, then column B must occur.

Syntax of Foreign Key

CREATE TABLE <Table Name>( 

column1    datatype,

column2    datatype,  

constraint (name of constraint) 

FOREIGN KEY [column1, column2..] 

REFERENCES [primary key table name] (List of primary key table column) ..);

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program

Use of Foreign Key

As mentioned earlier, the primary aim of using a foreign key is to enforce referential integrity and improve performance. This constraint is used for the prevention of any action that may result in the destruction of relations between two tables. The foreign key also helps in maintaining the data integrity of the table and allows easy navigation between two instances. It is also used in SQL to make the database data consistent.

Difference Between Primary Key and Foreign Key in SQL

Here are some key differences between primary key and foreign key in SQL:

SQL Foreign Key on CREATE TABLE

Before jumping on an example to create a foreign key on CREATE TABLE, let’s look at the syntax for the same, so that it would be easier to understand.

Syntax for Creating Foreign Key on CREATE TABLE-

CREATE TABLE child_table

(

  column1 datatype [ NULL | NOT NULL ],

  ...

  CONSTRAINT fk_name

    FOREIGN KEY (child_col1, ... child_col_n)

    REFERENCES parent_table (parent_col1 ... parent_col_n)

    [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 

);

child_table

It will be the name of the child table that we want to create.

column1...n

These will be the columns that we want to add to the child_table. We can add as many columns as we want and each column must have a data type defined. If no value is passed in these columns, it will be automatically assumed as NULL.

fk_name

This will be the name of the foreign key that we wish to create.

child_col1...n

These will be the columns that will be a reference to the primary key in the parent_table.

parent_col1...n

These are the columns that will serve as the primary key for the parent table.

Now, that we have seen the syntax, let’s have a look at an example.

Here, firstly we created the parent table as tracks. The parent table has a primary key, the field track_id. 

The next step we did is to create a child table as genre. CREATE TABLE statement is used to create a foreign key on the child table as fk_inv_track_id. This foreign key here will make a relation between the track_id of the parent table and track_id in the child table.

After executing the above query, both the tables will look something like this

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

SQL Foreign Key on ALTER TABLE

The syntax for creating a foreign key on ALTER TABLE is as given below.

ALTER TABLE child_table

ADD CONSTRAINT fk_name

    FOREIGN KEY (child_col1, child_col2, ... child_col_n)

    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n);

child_table

The table that has to be altered or modified.

fk_name

The name of the foreign key that we want to create.

Example-

In this example, we create a foreign key on the genre table that will reference the track_id in tracks table.

DROP a Foreign Key Constraint

To drop a foreign key, ALTER TABLE statement is used in SQL. Let’s look at the syntax given below.

Syntax

ALTER TABLE table_name

DROP CONSTRAINT fk_name;

table_name

This is the table name where the foreign key has been created.

Example-

In the very first example that we saw, we created a foreign key fk_inv_track_id on the table genre.

Using the below query, we can drop that foreign key.

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Now that we learned what a foreign key is and how it is created, let’s have a quick summary. Firstly, we looked at what is a foreign key. So, a foreign key in short is a constraint that helps to maintain a relationship between two tables in a database. After that, we saw some key differences between a primary and foreign key in SQL. And at last, we looked at how we can create a foreign key using CREATE and ALTER statements and also how to delete a previously created foreign key using the DROP statement. And if you wish to master SQL thoroughly, you must enroll in Simplilearn SQL certification training right away! 

Also, read our next tutorial on Composite Key to get more details about keys in SQL.

On the other hand, if you have any doubt on this topic, you can write to us in the comment section below, and our experts will help you out soon.