FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to link records of one table to the records of another. When you define a FOREIGN KEY constraint on a column, a column with the same name must exist as a primary key in another table. This enforces referential integrity since a foreign key value in one table cannot exist if it does not already exist as a primary key in another table. In the Create Manufacturers script, the foreign key column (ToyID) links the Manufacturers table to the Toys table. The CONSTRAINT, FOREIGN KEY, and REFERENCES keywords are used to define the foreign key. Although the ToyID column is defined near the top of the script, the definition of the ToyID foreign key can be placed at the end of the script. The name of the constraint (ToyFk) follows the CONSTRAINT keyword, and the name of the foreign key column (ToyID) follows FOREIGN KEY. The name of the linked table (Toys) and the primary key column (ToyID) from the linked table are defined after the REFERENCES keyword.

The ON UPDATE CASCADE and ON DELETE CASCADE keywords can be used with the FOREIGN KEY constraint to ensure that cascading updates and deletions occur. Cascading updates and deletions ensure referential integrity. For example, if you delete a manufacturer from the Manufacturers table, the manufacturer's product in the Toys table is deleted automatically.

The following script shows the specification of the FOREIGN KEY constraint from the Manufacturers table:

    CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys (ToyID)
    ON UPDATE CASCADE
    ON DELETE CASCADE

Note: The ON UPDATE CASCADE and ON DELETE CASCADE keywords can only be used in SQL-92. If you use one of these keywords in earlier Access SQL versions, it will return an error message.

UNIQUE Constraint

The PhoneNumber column in the Manufacturers table contains a UNIQUE constraint. The UNIQUE constraint is used to ensure that every value in a column is different. The UNIQUE constraint is very similar to the PRIMARY KEY constraint; however, the UNIQUE constraint can be defined for more than one field in a table, and a column defined as unique does not automatically default to NOT NULL. A table can only have one primary key.

To view the Manufacturers table, type the following script:

SELECT *
FROM Manufacturers;