FOREIGN KEY

A FOREIGN KEY constraint connects two tables by referencing the PRIMARY KEY of one table through a field or a set of fields in another table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL establishes a FOREIGN KEY on the “PersonID” column during the creation of the “Orders” table.

MySQL:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

For enabling the naming of a FOREIGN KEY constraint and specifying a FOREIGN KEY constraint across multiple columns, employ the subsequent SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

To add a FOREIGN KEY constraint on the “PersonID” column after the creation of the “Orders” table, utilize the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To add a FOREIGN KEY constraint on the “PersonID” column after the creation of the “Orders” table, utilize the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To remove a FOREIGN KEY constraint, employ the following SQL:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;