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.
This syntax is correct and does work properly, but in order for Access to accept it, the following requirements have to be fulfilled:
- You must be using Access 2003 and later.
- 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.