Get Started: SQL FOREIGN KEY Constraint

In This Article, You Will Know About SQL FOREIGN KEY Constraint. 

Before moving ahead, let’s know a bit about the SQL ALTER TABLE Statement.

Table of Contents

SQL FOREIGN KEY Constraint

The FOREIGN KEY Constraint is a column or the set of columns that establishes a link between data of two tables.

The FOREIGN KEY is a field in a table that refers to the PRIMARY KEY in another table.  

The table with PRIMARY KEY is considered the parent table and the table with FOREIGN KEY is considered the child table.

Table1: Student_Info

Serial Number

Name

Class

Roll_No

Subject

1

A

I

111

Eco

2

B

II

121

Math

3

C

III

131

Sci

4

D

IV

141

Eng

Table2: Student_Data

Serial Number

Address

Semester

Grade

Result

4

New Delhi

I

A+

P

3

Goa

II

AA+

P

2

Assam

III

B+

P

1

Kerala

IV

C+

F+

Notice the column “Serial Number” is the only same column represented in both tables.

The column “Serial Number” is the PRIMARY KEY in the table Student_Info.

The column “Serial Number” is the FOREIGN KEY in the table Student_Data.

Note: The FOREIGN KEY Constraint will always stop from inserting invalid values into FOREIGN KEY column because it can contain only one of those values which are presented in the PRIMARY KEY column.

SQL FOREIGN KEY – CREATE TABLE

MySQL Server 

Example: Create a FOREIGN KEY column called “Serial Number”.

				
					CREATE TABLE Student_Data (
	Address char(7) NOT NULL,
	Semester char(4) NOT NULL,
	Grade char(3) NOT NULL,
PRIMARY KEY (Serial Number),
	FOREIGN KEY (Result) REFERENCES Student_info(Serial Number)
);
				
			

SQL Server  

Example: Create a FOREIGN KEY column called “Serial Number”.

				
					CREATE TABLE Student_Data (
	Serial Number int NOT NULL PRIMARY KEY,
	Address char(7) NOT NULL,
	Semester char(4) NOT NULL,
	Grade char(3) NOT NULL,
	Result char(2) FOREIGN KEY REFERENCES Student_info(Serial Number)
);
				
			

Create a FOREIGN KEY on multiple columns after the table is created.

MySQL / SQL Server

Example: Create a FOREIGN KEY on multiple columns.

				
					CREATE TABLE Student_Data (
	Address char(7) NOT NULL,
	Semester char(4) NOT NULL,
	Grade char(3) NOT NULL,
    PRIMARY KEY (Serial Number),
	CONSTRAINT Table_Constraint FOREIGN KEY (Result) 
    REFERENCES Student_info(Serial Number)
);
				
			

SQL FOREIGN KEY – ALTER TABLE

Create a FOREIGN KEY after the table is created.

MySQL / SQL Server

Example: Use the FOREIGN KEY on the column Serial Number.

				
					ALTER TABLE Student_Info 
ADD FOREIGN KEY (Serial Number) REFERENCES Student_Data(Serial Number);
				
			

Create a FOREIGN KEY on multiple columns after the table is created.

MySQL / SQL Server

Example: Use the FOREIGN KEY on the multiple columns Serial Number.

				
					ALTER TABLE Student_Info
ADD CONSTRAINT Table_Constraint 
FOREIGN KEY (Serial Number) REFERENCES Student_Data(Serial Number);
				
			

DROP a FOREIGN KEY Constraint

Use the DROP to remove or delete a FOREIGN KEY Constraint.

MySQL Server 

Example: Use the DROP to remove the FOREIGN KEY constraint.

				
					ALTER TABLE Student_Info
DROP FOREIGN KEY Table_Constraint;
				
			

MySQL Server 

Example: Use the DROP to remove the FOREIGN KEY constraint.

				
					ALTER TABLE Student_Info
DROP FOREIGN KEY Table_Constraint;
				
			

SQL Server

Example: Use the DROP to remove the FOREIGN KEY constraint.

				
					ALTER TABLE Student_Info
DROP CONSTRAINT Table_Constraint;
				
			

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Leave a Comment

Stay in the loop

codingstreets