>
CONSTRAINT Clause
Description
A constraint is similar to an index, although it can
also be used to establish a relationship with another table.
You use the CONSTRAINT clause 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.
Note
The Microsoft Jet database engine doesn't support
the use of CONSTRAINT, or any of the data definition language
(DDL) statements, with non-Jet databases. Use the data access
object Create methods instead.
Syntax
Single-field index:
CONSTRAINT name {PRIMARY KEY |
UNIQUE |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]}
Multiple-field index:
CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable
[(foreignfield1 [, foreignfield2 [, ...]])]}
The CONSTRAINT clause has these parts.
| Part |
Description |
| |
|
| name |
The name of the constraint to be
created. |
| primary1, primary2 |
The name of the field or fields to be
designated the primary key. |
| unique1, unique2 |
The name of the field or fields to be
designated as a unique key. |
| ref1, ref2 |
The name of a foreign key field or
fields that refer to fields in another table. |
| foreigntable |
The name of the foreign table containing
the field or fields specified by foreignfield. |
| foreignfield1, foreignfield2 |
The name of the 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
You 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.
You 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:
- You can 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.
- You can 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
Don't set a PRIMARY KEY constraint on a table that
already has a primary key; if you do, an error occurs.
- You can 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 don't have to specify the referenced
fields — by default, the database engine behaves as
if the foreign table's primary key is the referenced
fields.
See Also
ALTER TABLE Statement, CREATE INDEX Statement,
CREATE TABLE Statement, DROP Statement.
Example
This example creates a new table called ThisTable
with two Text fields.
CREATE TABLE ThisTable (FirstName TEXT, LastName TEXT);
This example creates a new table called MyTable with
two Text fields, a Date/Time field, and a unique index made up of
all three fields.
CREATE TABLE MyTable (FirstName TEXT, LastName TEXT, DateOfBirth DATETIME, CONSTRAINT MyTableConstraint
UNIQUE (FirstName, LastName, DateOfBirth));
This example creates a new table with two Text
fields and an Integer field. The SSN field is the primary key.
CREATE TABLE NewTable (FirstName TEXT, LastName TEXT, SSN INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY);