In this article, we will learn how to use the Foreign Key constraint in SQL.
A FOREIGN KEY is a key, used to link tables together.
A FOREIGN KEY is a field(s) in one table that refers to the PRIMARY KEY in another table.
The FOREIGN KEY constraint is used to stop actions that can destroy links between tables.
The FOREIGN KEY constraint prevents invalid data to be inserted into the foreign key column because it must be one of the values contained in the table it points to.
A table containing the foreign key is called child table, and a table containing the candidate key is called a parent table or referenced table.
FOREIGN KEY – CREATE TABLE:
Please check How To Create Table Using Query In SQL, to get a brief description of the CREATE TABLE statement.
Syntax
CREATE TABLE TABLE_NAME (
column1 datatype,
column2 datatype,
....
columnN datatype,
CONSTRAINT CONSTRAINT_NAME FOREIGN KEY (column1, column2, ...columnN)
REFERENCES PARENT_TABLE_NAME (COLUMNS_OF_PARENTTABLE)
);
Example
The subsequent statement would create a FOREIGN KEY constraint on the “TechID” column when the “Article” table is created:
CREATE TABLE Article (
ID int PRIMARY KEY,
Title varchar(100),
Author varchar(100),
Views int,
TechID int,
CONSTRAINT FKArticle FOREIGN KEY (TechID)
REFERENCES Technology (ID)
);
FOREIGN KEY – ALTER TABLE:
Please check How To Alter Table Using Query In SQL, to get a brief description of the ALTER TABLE statement.
-
To ADD a FOREIGN KEY Constraint
Syntax
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME FOREIGN KEY (column1, column2, ...columnN) REFERENCES PARENT_TABLE_NAME (COLUMNS_OF_PARENTTABLE);
Example
The subsequent statement would add a FOREIGN KEY constraint on the “TechID” column using ALTER TABLE statement:
ALTER TABLE Article ADD CONSTRAINT FKArticle FOREIGN KEY (TechID) REFERENCES Technology(ID);
-
To DROP a FOREIGN KEY Constraint
Syntax
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME;
Example
The subsequent statement would delete a FOREIGN KEY constraint named “FKArticle” using ALTER TABLE statement:
ALTER TABLE Article DROP CONSTRAINT FKArticle;
Also, check How To Use Primary Key Constraint In SQL
