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