Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
should be moved to stackoverflow. –  Paul Nathan Jun 6 '10 at 16:29
    
Is there some reason why SuperUser is using "microsoft-access" as the tag, and StackOverflow uses "ms-access"? I can't see how having different tags is particularly useful. –  David W. Fenton Oct 3 '10 at 18:18

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
A foreign key doesn't have to be required, so CONSTRAINT NOT NULL is not necessary. –  David W. Fenton Jun 5 '10 at 22:56
    
I can't answer your question as I don't use DDL with Jet/ACE because DAO is the preferred interface. I understand that's not as convenient for you, but it's one of the reasons help and documentation for Jet/ACE DDL is not all that good, because it's the inferior method for creating/altering tables. –  David W. Fenton Jun 5 '10 at 22:57
    
I know that foreign keys are not always required, but some of my tables do require their foreign keys (due to the total participation of and entity in a relationship in the E-R model of the database). In those cases I need to specify that those foreign keys must NOT be NULL. However, I have already figured out the solution to my problem. Thank you, anyway! –  Genba Jun 6 '10 at 11:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.