CLAUSE: CONSTRAINT
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 is used to maintain data integrity by
providing limits on the values that can be inserted into a column or table.
While a CONSTRAINT clause is somewhat similar to an INDEX,
a CONSTRAINT can establish a relationship with another table.
To place a constraint on a single field in a CREATE TABLE or ALTER
TABLE statement, follow the definition of that field with a
CONSTRAINT clause. This consists
of a name for the constraint and one of the following reserved
words: PRIMARY KEY, UNIQUE, NOT NULL or REFERENCES.
The PRIMARY KEY reserved
word designates a field (or set of fields) as a primary key.
It is mandatory that all values in
the primary key must be unique and not NULL.
The following example sets the NameID field to be the primary key
of the Names table:
CREATE TABLE Names (NameID INTEGER CONSTRAINT NameIDKey PRIMARY KEY,
FirstName TEXT (20), LastName TEXT (20), DateOfBirth DATETIME);
An error will occur if you try to
use a PRIMARY KEY constraint on a table that
already has a primary key.
The reserved word UNIQUE requires that the value entered into the
specified field
(or combination of fields) be unique, as in the
following example which only allows unique first names:
CREATE TABLE Names (NameID INTEGER, FirstName TEXT (20)
CONSTRAINT UniqueName UNIQUE, LastName TEXT (20),
DateOfBirth DATETIME);
You can use the reserved word NOT NULL to specify that
a fields in a table must always contain valid data
(and cannot contain NULL):
CREATE TABLE Names (NameID INTEGER, FirstName TEXT
(20) NOT NULL, LastName TEXT (20) NOT NULL, DateOfBirth DATETIME NOT
NULL)
You can establish a relationship with a field in a foreign table (as
long as it only contains unique values) by using the reserved word
REFERENCES and naming that foreign table and the field:
CREATE TABLE Sales (SalesID INTEGER,
ProductID INTEGER, Item TEXT CONSTRAINT ForeignRefs
REFERENCES Products (Item) );
...or if the field in the foreign table is the
primary key, you only need name the table and the database engine
references it by default:
CREATE TABLE Sales (SalesID INTEGER,
ProductID INTEGER CONSTRAINT ForeignKeyRef
REFERENCES Products, Item TEXT);
When you want to apply a constraint to more than
one field (a multiple-field constraint),
you can do so by adding the CONSTRAINT clause
after all the field definitions.
The next example makes the two fields FirstName and
LastName a joint primary key:
CREATE TABLE Names (NameID INTEGER, FirstName TEXT (20), LastName TEXT
(20), DateOfBirth DATETIME,
CONSTRAINT NameKey PRIMARY KEY(FirstName,
LastName) );
...whereas this next one requires the combination
of FirstName, LastName and DateOfBirth to be unique:
CREATE TABLE Names (NameID INTEGER, FirstName TEXT
(20), LastName TEXT (20), DateOfBirth DATETIME,
CONSTRAINT UniqueFields
UNIQUE(FirstName, LastName, DateOfBirth) );
Note that it is acceptable for one or more of the fields
in a multiple-field
constraint to contain values that are the same, as long as
the combination of values in all the constrained fields is unique.
If you want to include a FOREIGN KEY that consists of more than one
field, you must use a multiple-field constraint definition. The constraint
definition must list the names
of the referencing fields, the foreign table and the referenced fields in
the foreign table. The order of the referenced fields must correspond
to the order of the refering fields:
CREATE TABLE Albums (AlbumID INTEGER, AlbumName TEXT, TuneName TEXT,
TuneType TEXT,
CONSTRAINT ReferForeignField FOREIGN KEY(TuneName,
TuneType) REFERENCES Tunes (Name, Type) );
Microsoft warns, "The Microsoft Jet database engine doesn't support the use of any DDL
statements with databases produced by any other database engine.
Use the DAO (Data Access Objects) Create methods instead."
Copyright 2000 by Infinite Software Solutions, Inc.
Trademark Information
|