Foreign Key in SQL Usage Explained [Practical Examples]

What is Foreign Key in SQL?

Foreign Key in SQL can be specified as the referential integrity in Relational Database management system, in technical term foreign key in SQL is a constraint which links attribute of one table with the attribute of another table, A foreign key is an attribute or collection of attributes of one table, that refers to the Primary Key attribute of another table

A foreign Key in SQL links two and more tables by referencing its primary key attribute, foreign key constraint specifies that the key can only contain values that are in the referenced primary key attribute of parent table and thus ensures the referential integrity of data that is joined on the two keys primary key and foreign key

 

SQL Foreign Key Constraint features

Foreign Key in SQL can be specified while creating table with SQL Create Table statement and can be identify in existing table with SQL Alter Table statement

The Foreign key specification says that the values of attributes (column1,…columnN) for any tuple in the relation or in table must have correspond to values of the primary key attributes of some tuple in primary key table or in a relation

In relational database system, the table which contains Primary key is considered as the parent table and the table which reference the parent table with one or more foreign keys is consider as the child table in SQL

 

Point to Note with SQL Foreign Key Constraint

• Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as integer and decimal must be the same. The length of string types need not be the same
• Parent and child table must be in the same database engine

 

Difference between Foreign Key in SQL & Primary Key in SQL

• We can create only one PRIMARY KEY in SQL per each table, with the ability to create multiple SQL FOREIGN KEY constraints in each table by referencing multiple parent table.
• The FOREIGN KEY constraint allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY constraint does not accept NULLs.
• A primary key may exist on its own do not need any referential table to refer where as foreign key must always reference to a primary key somewhere where the original table containing the primary key is the parent table (also known as referenced table). This key can be referenced by multiple foreign keys from other tables, known as “child” tables

The FOREIGN KEY constraint can be defined with CREATE TABLE T-SQL statement, or it can be added after the table creation using the ALTER TABLE T-SQL statement. We will create more than one tables to understand the FOREIGN KEY constraint functionality.

 

SQL Foreign Key Constraint Syntax

There are mainly three ways to define foreign key constraint on table in SQL

Column_name <datatype> FOREIGN KEY REFERENCES parent_table_name (parent_table_column_name) 
[ON DELETE reference_option] [ON UPDATE reference_option]

In the above syntax, column_name is the name of column on which we need to define foreign key constraint, FOREIGN KEY and REFERENCES are the keywords, parent_table_name is the name of the primary table, parent_table_column_name is the name of the parent table column having primary key constraint which will be the referenced key column

FOREIGN KEY (Column_name) REFERENCES parent_table_name(parent_table_column_name) 
[ON DELETE reference_option] [ON UPDATE reference_option]

In this Syntax, column_name is foreign key column name, parent_table_name is the name of the primary table, parent_table_column_name is the name of the parent table column having primary key constraint which will be the referenced key column

CONSTRAINT Constraint_name FOREIGN KEY (Column_name) REFERENCES parent_table_name (parent_table_column_name) [ON DELETE reference_option] [ON UPDATE reference_option]

CONSTRAINT Constraint_name FOREIGN KEY (column_name1,column_name2) REFERENCES parent_table_name (parent_table_column_name)
[ON DELETE reference_option] [ON UPDATE reference_option]

• The difference in above two syntax is, using CONSTRAINT keyword, we need to specify constraint name that is user-defined, column_name is the same as above foreign key column name and the parent_table name and column name of primary key column of parent table
• Foreign key can me defined on more than one columns of child table for that column names is specified with FOREIGN KEY keywords separated by comma

 

Reference Options associated with a SQL foreign key Constraint

1. Cascade: It is used in conjunction with ON DELETE or ON UPDATE statement, if the records of a parent table with referenced attribute primary key are deleted or updated then referencing rows in the child table will be deleted
2. Set NULL: It is used in conjunction with ON DELETE or ON UPDATE. If the record is deleted or updated in parent table the child table data is set to NULL
3. Set Default: It is used in conjunction with ON DELETE or ON UPDATE. When the record is deleted or updated in parent table, the child table data is set to their default values.
4. No Action: It is used in conjunction with ON DELETE or ON UPDATE. When the record in the parent table is deleted or updated than referencing records in the child table will not be affected

 

SQL Foreign Key Constraint with Create Table Statement

Consider Hospital Management Database with four tables as following

In, Above Hospital Database, patient and doctor tables are the parent tables with the primary key attribute patient_id and doctor_id respectively, where as bill and laboratory both tables are the child tables having foreign key constraint

 

Example-1: SQL Foreign Key Constraint on single column

Create Table bill with foreign key constraint on attribute patient_id of referenced table patient

CREATE TABLE [dbo].[bill] (
    [bill_no]       INT NOT NULL,
    [doctor_id]     INT NOT NULL,
    [patient_id]    INT NULL,
    [doctor_charge] INT NOT NULL,
    [room_charge]   INT NULL,
    [no_of_days]    INT CONSTRAINT [DF__bill__no_of_days] DEFAULT ((1)) NULL,
    CONSTRAINT [PK__bill] PRIMARY KEY CLUSTERED ([bill_no] ASC),
    CONSTRAINT [FK__bill__patient_id] FOREIGN KEY ([patient_id]) REFERENCES [dbo].[patient] ([patient_id])
);

• In above SQL create table statement, three constraints have been defined first is the primary key, second is the Default and third one is the foreign key constraint
• To define the foreign key on an attribute, that attribute must be the primary key attribute of any table, so here patient_id is the primary key column of table patient
• CONSTRAINT and REFERENCES keywords are used to define foreign key on attribute

 

Example 2: SQL Foreign Key Constraint on two columns

Define foreign key constraint for laboratory table on two attributes which references to the parent table patient and doctor

CREATE TABLE [dbo].[laboratory] (
    [lab_no]     INT  NOT NULL,
    [patient_id] INT  NULL,
    [doctor_id]  INT  NULL,
    [date]       DATE NULL,
    [amount]     INT  NOT NULL,
    PRIMARY KEY CLUSTERED ([lab_no] ASC),
    FOREIGN KEY ([patient_id]) REFERENCES [dbo].[patient] ([patient_id]),
    FOREIGN KEY ([doctor_id]) REFERENCES [dbo].[doctor] ([doctor_id]),
    CHECK ([amount]>(0))

• In above create table query, lab_no is the primary key column whereas the Foreign Key constraint is applied on two columns of laboratory table patient_id and doctor_id using FOREIGN KEY and REFERENCES keywords
• Patient_id and doctor_id are the Primary key attribute of Referenced table patient and doctor

 

SQL Foreign Key Constraint with Alter Table

• The Foreign Key constraint can be add or remove on existing table of a relation database by defining it with SQL Alter table statement
• With ALTER TABLE we need to use ADD CONSTRAINT while in CREATE TABLE you need to use only CONSTRAINT keyword

 

Syntax to add Foreign Key Constraint on Existing Table

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name1, column_name2...)
REFERENCES parent_table_name(column_name)

 

Example-3: Add Foreign Key Constraint on Existing Table

Add the foreign key constraint on doctor_id column of previously created table bill

ALTER TABLE [dbo].[bill] 
ADD CONSTRAINT [FK__doctor__ID] 
FOREIGN KEY (doctor_id) 
REFERENCES doctor(doctor_id);

• In above example , foreign key constraint named FK_doctor_ID is defined on column doctor_id which has referenced key in doctor table
• ADD CONSTRAINT keyword is used with ALTER TABLE statement to add foreign key constraint on doctor_id column

 

Example-4: Foreign Key in SQL add with DELETE and UPDATE Cascade

Adding foreign key constraint on existing table bill with on cascade delete and on cascade update

ALTER TABLE [dbo].[bill] 
ADD CONSTRAINT [FK__doctor__ID] 
FOREIGN KEY (doctor_id) 
REFERENCES doctor(doctor_id);
ON DELETE CASCADE
ON UPDATE CASCADE

If ON DELETE CASCADE and ON UPDATE CASCADE is added with foreign key constraint, when the record is deleted or updated in parent table doctor in primary key doctor_id the corresponding record will be removed or updated in foreign key table that is bill table

 

Syntax to remove Foreign Key Constraint on Existing Table

In below Syntax of remove SQL foreign Key constraint, DROP CONSTRAINT Keyword is used followed by constraint name with SQL Alter table statement

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

 

Example-5: Remove Foreign Key Constraint on Existing Table

Drop foreign key constraint patient_id from previously created bill table

ALTER TABLE bill
DROP CONSTRAINT FK__bill__patient_id;

Summary

In this Article of Foreign Key in SQL, SQL Foreign Key constraint has covered with syntax, difference between SQL Foreign key constraints with Primary Key Constraint, various foreign key reference action and its usage has been explained, In second part of this article the practical examples of how to defined foreign key with Create Table and Alter table statement with syntax and examples has been covered

 

References

SQL Create Table
NOT NULL Constraint in SQL

 

Further Reading

Create Foreign Key Relationships