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:
