What is Foreign Key in SQL

SQL is a very popular programming language and in this tutorial, you will learn everything about SQL foreign key Let’s start!

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

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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:

Primary Key

Foreign Key

A primary key constraint is used to uniquely identify a record in the database

A foreign key is a field in a table that is the primary key in another table

A primary key can never accept a NULL value

Foreign key can be NULL

There is only one primary key for each table

There can be multiple foreign keys for a single table in the database

Since the primary key is used to uniquely identify a record, no two rows can have the same value for a primary key

Foreign key can contain the same or duplicate values

A primary key is related to a single table only

A foreign key is related to two or more tables at the same time

It is a clustered index, by default

By default, it is not a clustered index

A primary key constraint can be defined on temporary tables

Foreign key constraint can not be defined on a temporary table

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.

ForeignkeyinSQL_1

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

ForeignkeyinSQL_2

ForeignkeyinSQL_3

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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-

ForeignkeyinSQL_4

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.

Foreignkeyin%20SQL_5

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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.