MS-Access / Getting Started

Constraint Syntax

In Microsoft Access, the ALTER TABLE statement can be used to add any of the constraints discussed in this tutorial to an existing table. The following shows the SQL syntax to add PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints to an existing table:

ALTER TABLE Tablename
ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName);

ALTER TABLE Tablename
ADD CONSTRAINT ConstraintName FOREIGN KEY (ColumnName)
REFERENCES LinkedTableName (PrimaryKey);

In the FOREIGN KEY constraint syntax, the table name and primary key column from the linked table are defined after the REFERENCES keyword.

ALTER TABLE Tablename
ALTER COLUMN ColumnName Datatype (Field size) NOT NULL;

The syntax to add the NOT NULL constraint to an existing table is slightly different from other constraints. To add a NOT NULL constraint to an existing table you use the ALTER COLUMN keywords, which are used to specify the column name, data type, field size, and the NOT NULL keywords.

[Previous] [Contents] [Next]