How can I specify rules for foreign keys in Microsoft Access using SQL?

Question

I have to create some tables in Microsoft Access using SQL sentences, and some of the tables have foreign keys.

I would like to specify the constraints and rules for foreign keys using SQL:

1. Whether NULL is allowed or not
2. Delete rule
3. Update rule

How can I specify rules for foreign keys in Microsoft Access using SQL?

Answer 1

I have finally found something in Access' documentation.

The CONSTRAINT clause for defining a FOREIGN KEY constraints accepts the following syntax:

CONSTRAINT FOREIGN KEY (ref1[, ref2[, ...]]) REFERENCES table
    ON UPDATE [CASCADE | SET NULL]
    ON DELETE [CASCADE | SET NULL]

I suppose that, in order to specify whether NULL is allowed or not for that FOREIGN KEY, a different constraint has to be added with

CONSTRAINT NOT NULL (ref)

or directly by specifyng NOT NULL when defining that field in the CREATE TABLE clause.

However, I get a syntax error when I enter "ON UPDATE" and "ON DELETE" telling that are not accepted, and that error does not appear when I remove those rules.

---

Edited 2010-06-06:

This syntax is correct and does work properly, but in order for Access to accept it, the following requirements have to be fulfilled:

1. You must be using Access 2003 and later.
2. In menu "Tools > Options", on tab "Tables or queries", you must specify that you want "Syntax compatible with SQL Server (ANSI 92)" at least for current database.

After that little configuration in my Access 2003, it now works! Access does not show that error message anymore, and it now allows that syntax for specifying the delete and update rules for foreign keys!

When either of those rules is not specified, Access assumed that it is restricted. In other words, if the delete rule or the update rule is not specified, Access restricts deletion or update, respectively.