SQL FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two
tables:
The "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 |
The "Orders" table:
O_Id |
OrderNo |
P_Id |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
2 |
4 |
24562 |
1 |
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 a FOREIGN KEY in the "Orders"
table.
The FOREIGN KEY constraint is used to prevent actions that would destroy link
between tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into
the foreign key column, because it has to be one of the values contained in the
table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY 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 allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint
on multiple columns, 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 Constraint on ALTER TABLE
To create a FOREIGN KEY constraint on the "P_Id" column when the
"Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
|
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint
on multiple columns, 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)
|
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
|
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
|
|
|
Get Your Diploma!
W3Schools' Online Certification Program is the perfect solution for busy
professionals who need to balance work, family, and career building.
The HTML Certificate is for developers who want to document their knowledge of HTML, XHTML, and CSS.
The JavaScript Certificate is for developers who want to document their knowledge of JavaScript and the HTML DOM.
The XML Certificate is for developers who want to document their knowledge of XML, XML DOM and XSLT.
The ASP Certificate is for developers who want to document their knowledge of ASP, SQL, and ADO.
The PHP Certificate is for developers who want to document their knowledge of PHP and SQL (MySQL).
|
|