PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify every record in a table. The specification of a primary key ensures that there are no duplicate values in a column. Additionally, primary key fields are stored in ascending order and default to NOT NULL.

In the Create Toys script, the ToyID column contains a PRIMARY KEY constraint. The CONSTRAINT and PRIMARY KEY keywords are used to define the primary key. The name of the constraint (ToyPk) follows the CONSTRAINT keyword. Primary keys can also be defined using only the PRIMARY KEY keywords; however, this method does not enable you to assign a name to your primary key constraint. Assigning a name to your PRIMARY KEY constraint is vital because it makes it easier for you to update the constraint if necessary. To view the new Toys table, type the following script:

SELECT *
FROM Toys;

This script uses a SELECT statement to retrieve records from a table. The SELECT keyword combined with an asterisk (*) instruct Microsoft Access to retrieve all the columns from a table. The FROM keyword instructs Microsoft Access to retrieve the records from the Toys table.

Example-C

You want to link the Toys table in Example-B to a new table named Manufacturers. Additionally, you want to ensure that all phone numbers entered into the PhoneNumber column in the Manufacturers table are unique and that all updates and deletions made to the Manufacturers table affect corresponding records in the Toys table. Take a look at the following script:

    CREATE TABLE Manufacturers
    (
    ManufacturerID INTEGER CONSTRAINT ManfID PRIMARY KEY,
    ToyID INTEGER NOT NULL,
    CompanyName CHAR (50) NOT NULL,
    Address CHAR (50) NOT NULL,
    City CHAR (20) NOT NULL,
    State CHAR (2) NOT NULL,
    PostalCode CHAR (5) NOT NULL,
    AreaCode CHAR (3) NOT NULL,
    PhoneNumber CHAR (8) NOT NULL UNIQUE,
    CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys (ToyID)
    ON UPDATE CASCADE
    ON DELETE CASCADE
    );

Note: Make sure you have created the Toys table in Example-B (in previous section) before you create the Manufacturers table since it contains the foreign key reference to the Toy table.

The preceding SQL script creates a table named Manufacturers with nine columns (ManufacturerID, ToyID, CompanyName, Address, City, State, PostalCode, AreaCode, PhoneNumber). A PRIMARY KEY constraint is defined for the ManufacturerID column, and the NOT NULL constraint is defined for all other columns. The PhoneNumber column contains a UNIQUE constraint and the ToyID column contains a FOREIGN KEY constraint. Following is an explanation of the ON UPDATE CASCADE and ON DELETE CASCADE keywords and the UNIQUE and FOREIGN KEY constraints used in Example-C.