CONSTRAINT Clause

See Also

Use in ALTER TABLE and CREATE TABLE statements to create or delete constraints. There are two types of CONSTRAINT clauses: one for creating a constraint on a single field and one for creating a constraint on more than one field.

A constraint is similar to an index, although it can also be used to establish a relationship with another table.

Note The Microsoft Jet database engine does not support the use of CONSTRAINT, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases. Use the DAO Create methods instead.

Syntax

Single-field constraint:

CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]}

Multiple-field constraint:

CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
NOT NULL (notnull1[, notnull2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]}

The CONSTRAINT clause has these parts:

Part	The Name of the
name	Constraint to be created.
primary1, primary2	Field or fields to be designated the primary key.
unique1, unique2	Field or fields to be designated as a unique key.
notnull1, notnull2	Field or fields that are restricted to non-Null values.
ref1, ref2	Foreign key field or fields that refer to fields in another table.
foreigntable	Foreign table containing the field or fields specified by foreignfield.
foreignfield1, foreignfield2	Field or fields in foreigntable specified by ref1, ref2. You can omit this clause if the referenced field is the primary key of foreigntable.

Remarks

Use the syntax for a single-field constraint in the field-definition clause of an ALTER TABLE or CREATE TABLE statement immediately following the specification of the field's data type.

Use the syntax for a multiple-field constraint whenever you use the reserved word CONSTRAINT outside a field-definition clause in an ALTER TABLE or CREATE TABLE statement.

Using CONSTRAINT, you can designate a field as one of the following types of constraints:

Use the UNIQUE reserved word to designate a field as a unique key. This means that no two records in the table can have the same value in this field. You can constrain any field or list of fields as unique. If a multiple-field constraint is designated as a unique key, the combined values of all fields in the index must be unique, even if two or more records have the same value in just one of the fields.
Use the PRIMARY KEY reserved words to designate one field or set of fields in a table as a primary key. All values in the primary key must be unique and not Null, and there can be only one primary key for a table.
Note Do not set a PRIMARY KEY constraint on a table that already has a primary key; if you do, an error occurs.
Use the FOREIGN KEY reserved words to designate a field as a foreign key. If the foreign table's primary key consists of more than one field, you must use a multiple-field constraint definition, listing all of the referencing fields, the name of the foreign table, and the names of the referenced fields in the foreign table in the same order that the referencing fields are listed. If the referenced field or fields are the foreign table's primary key, you do not have to specify the referenced fields — by default, the database engine behaves as if the foreign table's primary key is the referenced fields.