SQL FOREIGN KEY

SQL FOREIGN KEY constraint

The FOREIGN KEY in one table points to the PRIMARY KEY in another table.

Let us explain foreign keys through an example. Please look at the following two tables:

"Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

"Orders" table:

##O_IdOrderNoP_Id 177895324467833224562##4

Please note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is the FOREIGN KEY in the "Orders" table.

FOREIGN KEY constraints are used to prevent behavior that destroys connections between tables.

FOREIGN KEY constraint also prevents illegal data from being inserted into a foreign key column because it must be one of the values in the table it points to.

SQL FOREIGN KEY constraint when CREATE TABLE

The following SQL creates a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is created:

MySQL：

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)
)

##SQL Server / Oracle / MS Access:

CREATE TABLE Orders

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

To name the FOREIGN KEY constraint and define FOREIGN KEY constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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)
)

SQL FOREIGN KEY constraints when ALTER TABLE

when" Orders" table has been created, if you need to create a FOREIGN KEY constraint on the "P_Id" column, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

If you need to name the FOREIGN KEY constraint and define FOREIGN for multiple columns KEY constraints, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Revoke FOREIGN KEY constraint

If you need to revoke FOREIGN KEY constraint, please use SQL below:

MySQL:

ALTER TABLE Orders

DROP FOREIGN KEY fk_PerOrders

SQL Server/Oracle/MS Access:

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

← SQL PRIMARY KEY

SQL CHECK →

submit

php.cn





24562	1