DevGuru Jet SQL CONSTRAINT

Quick References

Features

Partners


			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."