SQL FOREIGN KEY Constraint | Newbie Tutorial

Newbie Tutorial -- Learning Technology, More Than Just Dreams!

• Home
• HTML
• JavaScript
• CSS
• Vue
• React
• Python3
• Java
• C
• C++
• C#
• AI
• Go
• SQL
• Linux
• VS Code
• Bootstrap
• Git
• Local Bookmarks

• Home
• HTML
• CSS
• JS
• Local Bookmarks
• Search

• Vue3 Tutorial
• Vue2 Tutorial

• Bootstrap3
• Bootstrap4
• Bootstrap5

• Machine Learning
• PyTorch
• TensorFlow
• Sklearn
• NLP
• AI Agent
• Ollama
• Coding Plan

SQL Tutorial

SQL TutorialSQL IntroductionSQL SyntaxSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND & ORSQL ORDER BYSQL INSERT INTOSQL UPDATESQL DELETE

SQL Advanced Tutorial

SQL SELECT TOPSQL LIKESQL WildcardsSQL INSQL BETWEENSQL AliasesSQL JOINSQL INNER JOINSQL LEFT JOINSQL RIGHT JOINSQL FULL JOINSQL UNIONSQL SELECT INTOSQL INSERT INTO SELECTSQL CREATE DATABASESQL CREATE TABLESQL ConstraintsSQL NOT NULLSQL UNIQUESQL PRIMARY KEYSQL FOREIGN KEYSQL CHECKSQL DEFAULTSQL CREATE INDEXSQL DROPSQL ALTERSQL Auto IncrementSQL ViewsSQL DatesSQL NULL ValuesSQL NULL FunctionsSQL General Data TypesSQL DB Data Types

SQL Functions

SQL FunctionsSQL AVG()SQL COUNT()SQL FIRST()SQL LAST()SQL MAX()SQL MIN()SQL SUM()SQL UCASE()SQL LCASE()SQL MID()SQL LEN()SQL ROUND()SQL NOW()SQL FORMAT()

---

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the referenced primary key is called the referenced or parent table.

The purpose of the foreign key is to ensure referential integrity of the data. In other words, it ensures that the values in the foreign key must correspond to values in the referenced primary key.

CREATE TABLE Example

The following SQL creates two tables - "Persons" and "Orders". These two tables are linked together using the "P_Id" column:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

The example above creates a FOREIGN KEY on the "Orders" table when the "Orders" table is created. The foreign key refers to the "P_Id" column in the "Persons" table.

Note: We can also name a constraint by adding CONSTRAINT keyword followed by the name of the constraint.

The example below creates the same "Orders" table as above, but with a named constraint:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

ALTER TABLE Example

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table already exists, use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To create a named FOREIGN KEY constraint, use the following SQL syntax:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

For MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

For SQL Server, Oracle, MySQL:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders