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-Microsoft Jet databases. Use the DAO Create methods instead. Syntax Single-field constraint: CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |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. |
notnull1, notnull2 | The name of the field or fields that are restricted to non-Null values. |
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:Sub CreateTableX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with two text fields.
dbs.Execute "CREATE TABLE ThisTable " _
& "(FirstName TEXT, LastName TEXT);"
dbs.Close
End Sub
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.
Sub CreateTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with three fields and a unique
' index made up of all three fields.
dbs.Execute "CREATE TABLE MyTable " _
& "(FirstName TEXT, LastName TEXT, " _
& "DateOfBirth DATETIME, " _
& "CONSTRAINT MyTableConstraint UNIQUE " _
& "(FirstName, LastName, DateOfBirth));"
dbs.Close
End Sub
This example creates a new table with two Text fields and an Integer field. The SSN field is the primary key.
Sub CreateTableX3()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Create a table with three fields and a primary
' key.
dbs.Execute "CREATE TABLE NewTable " _
& "(FirstName TEXT, LastName TEXT, " _
& "SSN INTEGER CONSTRAINT MyFieldConstraint " _
& "PRIMARY KEY);"
dbs.Close
End Sub
Example (Microsoft Access)
To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.
CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT);
The next example creates a new table with two Text fields, a Date/Time field, and a unique index made up of all three fields:
CREATE TABLE SecondTable (FirstName TEXT,
LastName TEXT, DateOfBirth DATETIME,
CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));
The following example creates a new table with two Text fields and an Integer Number field. The SSN field is the primary key.
CREATE TABLE ThirdTable (FirstName TEXT, LastName TEXT, SSN INTEGER
CONSTRAINT MyFieldConstraint PRIMARY KEY);