MS-Access / Getting Started

Constraints

Constraints enable you to further control how data is entered into a table and are used to restrict values that can be inserted into a field and to establish referential integrity. Recall that referential integrity is a system of rules used to ensure that relationships between records in related tables are valid. Table-5 explains the constraints available in Microsoft Access.

Table-5. Microsoft Access constraints
ConstraintDescription
NULL/NOT NULLUsed to indicate if a field can be left blank when records are entered into a table.
PRIMARY KEYUsed to uniquely identify every record in a table.
FOREIGN KEYUsed to link records of a table to the records of another table.
UNIQUEUsed to ensure that every value in a column is different.
CHECKUsed to set criterion for the data entered into a column.

Now take a look at the following examples, which implement the constraints described in Table-5.

Example-B

You want to alter the Toys table script created earlier in the tutorial. You want to add constraints that will ensure that every Toy ID is unique and that the ToyID, ToyName, and Price columns always contain values when new records are entered into the Toys table. Look at the following script:

    CREATE TABLE Toys
    (
    ToyID INTEGER CONSTRAINT ToyPk PRIMARY KEY,
    ToyName CHAR (30) NOT NULL,
    Price MONEY NOT NULL,
    Description CHAR (40) NULL
    );

This script creates a new table named Toys with four columns (ToyID, ToyName, Price, and Description). A primary key constraint is defined for the ToyID column and the NOT NULL constraint is defined for the ToyName and Price columns. The Description column contains a NULL constraint. Following is an explanation of the NULL/NOT NULL and primary key constraints.

[Previous] [Contents] [Next]