The FOREIGN KEY keywords are a key (a field or fields in one table that refers to the PRIMARY KEY in another table) used to link two tables together.

The following creates a FOREIGN KEY on the rep_id column when the Orders table is created.

MySQL:

CREATE TABLE Orders (
    order_id int NOT NULL,
    order_number int NOT NULL,
    rep_id int,
    PRIMARY KEY (order_id),
    FOREIGN KEY (rep_id) REFERENCES Persons(rep_id)
);

SQL Server:

CREATE TABLE Orders (
    order_id int NOT NULL PRIMARY KEY,
    order_number int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(rep_id)
);

The following allows the naming of a FOREIGN KEY constraint, and defines a FOREIGN KEY constraint on multiple columns.

MySQL / SQL Server:

CREATE TABLE Orders (
    order_id int NOT NULL,
    order_number int NOT NULL,
    rep_id int,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_order FOREIGN KEY (rep_id)
    REFERENCES Persons(rep_id)
);

The following creates a FOREIGN KEY constraint on the rep_id column when the Orders table already exists.

MySQL / SQL Server:

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

The following allows the naming of a FOREIGN KEY constraint, and defines a FOREIGN KEY constraint on multiple columns.

MySQL / SQL Server:

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

The following deletes the foreign key constraint named fk_customer_names from the Customers table.

MySQL:

ALTER TABLE Customers
DROP FOREIGN KEY fk_customer_names;

SQL Server:

ALTER TABLE Customers
DROP CONSTRAINT fk_customer_names;

---