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

Constraint	Description
NULL/NOT NULL	Used to indicate if a field can be left blank when records are entered into a table.
PRIMARY KEY	Used to uniquely identify every record in a table.
FOREIGN KEY	Used to link records of a table to the records of another table.
UNIQUE	Used to ensure that every value in a column is different.
CHECK	Used 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]

Constraints

Example-B

In this tutorial: