SQL Foreign Key Constraint: The Complete Guide

0
21
SQL Foreign Key Constraint Tutorial With Example

SQL foreign key is used to form a link between two tables, which makes it a referencing key. The foreign key is a constraint, a column, or a combination of the column used to point to the primary key of another table. If the table has the primary key defined on any field(s), then a user cannot have the two records having the exact value of that field(s).

SQL Foreign Key

The primary purpose of the foreign key is that only those values will appear in the columns present in the primary key table and a  foreign key is a referencing key in one table. Therefore, the foreign key must match an existing primary key in the referenced table. This enforcement of foreign key is known as referential integrity.

Let’s understand the use of a foreign key with an example.

We are going to create a foreign key with create keyword.

See the following syntax.

Create table table_name(
Column1 datatype,
Column2 datatype,
……..,
Column(n) datatype,
Constraint (constraint name)
FOREIGN KEY [column1, column2…]
REFERENCES [primary_key_table] (column_list_of_primary_key_table) ….);

#PARAMETERS

  1. Table_name: Name of the table
  2. Column(n) datatype: Name of the column associated with its datatype.
  3. Constraint: This is a keyword, and this is optional.
  4. Constraint name: It is a constraint name defined by a user.
  5. Primary_key_table: This is a table where the primary key resides.
  6. column_list_of_primary_key_table: List of columns used to make the primary key in a table.

See the following query.

Create table customers (
Id integer primary key,
Name varchar(20),
Age integer,
Address varchar(25),
Salary integer);

We created a customer table with ID as a primary key in the above statements.

Let’s create a Table that will have foreign keys.

Create table orders (
Id integer,
Date date,
Cust_id integer foreign key references Customers(Id),
Amount integer,
Primary key(Id));

In the above statements, we have created the orders table with ID as the primary key and Cust_id as the reference key, i.e., a foreign key that refers to the primary key of the customers’ table.

Creating a foreign key using ALTER statement

This is used when a table is created without making any column as a foreign key. Using the alter statement, we can create a foreign key. Then to do that, use the following Syntax.

Alter table table_name 
ADD foreign key(column) 
references refer_table_name(Primary_key_of_refer_table);

#PARAMETERS

  • Table_name: Name of the table which will contain the foreign key.
  • ADD foreign key: Used for creating a foreign key.
  • Foreign key(Column): Used for creating a column which will be the foreign key.
  • Refer_table_name: Name of the table which will be referred.
  • Primary_key_of_refer_table: Primary key of the table which is referred to.

Consider previously created table orders. Suppose that the orders table doesn’t contain the foreign key. So, to make a foreign key, use the following statements.

Let’s create it.

Alter table orders 
ADD foreign key(Cust_id) 
REFERENCES CUSTOMERS(Id);

By the above statements, a foreign key name cust_id is created, referring to the primary key, i.e., the ID of the customers’ table.

#Dropping A Foreign Key

Suppose we want to delete the foreign key. Then to do that, use the following syntax.

ALTER TABLE table_name DROP foreign key foreign_key_name;

#PARAMETERS

  1. table_name: The name of the table which is containing the primary key.
  2. DROP: Name of the keyword which is used for dropping.
  3. Foreign key: Name of the keyword which will identify the foreign key.
  4. Foreign_key_name: Name of the foreign key.

Suppose we want to delete a previously created foreign key.

Use the following statements.

ALTER TABLE orders DROP foreign key cust_id;

The above statement deleted the orders table foreign key, cust_id.

That’s it for this tutorial.

Leave A Reply

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.