使用ON DELETE CASCADE定义表时的语法错误 [英] Syntax error when defining table with ON DELETE CASCADE

问题描述

我试图在MS Access 2007的FK约束中使用ON DELETE CASCADE，但是在表定义上却出现错误:

I'm trying to use ON DELETE CASCADE in a FK constraint in MS Access 2007, but I'm getting an error on table definition:

SQL错误:CONSTRAINT子句中的语法错误.

SQL Error: Syntax error in CONSTRAINT clause.

以下是用于创建表的代码:

Here's the code for creating the table:

CREATE TABLE Area (
    Id AUTOINCREMENT PRIMARY KEY, 
    AreaType__Id int NOT NULL, 
    Tbl1 text(31) NOT NULL, 
    Tbl2__Id int NOT NULL, 
    CONSTRAINT UK_Area_1 UNIQUE (Tbl1, Container__Id), 
    CONSTRAINT FK_Area_1 FOREIGN KEY (AreaType__Id) REFERENCES AreaType (Id), 
    CONSTRAINT FK_Area_2 FOREIGN KEY (Tbl2__Id) REFERENCES Tbl2 (Id) ON UPDATE CASCADE ON DELETE CASCADE
);

我做错了什么?我查看了访问帮助，我的语法似乎正确.我尝试删除ON UPDATE CASCADE部分，但是遇到了相同的错误.我也尝试将默认的PK字段用于被引用的表(REFERENCES Container而不是REFERENCES Container (Id))，但是再次遇到相同的错误.我也搜索了SO，但没有找到适合我的情况的有用信息.它一定很简单，但是我暂时没有看到它.

What am I doing wrong? I looked at the Access Help, and my syntax seems correct. I've tried removing the ON UPDATE CASCADE portion, but got the same error. I've also tried using the default PK field for the referenced table (REFERENCES Container instead of REFERENCES Container (Id)), but again got the same error. I also searched SO, but didn't find much useful info for my situation. It's got to be something simple, but I'm not seeing it at the moment.

编辑

值得一提的是，除了没有ON UPDATE CASCADE ON DELETE CASCADE部分之外，表定义可以完全按原样正常工作.仅在添加CASCADE部分后，错误才会出现.

It's worth mentioning, that the table definition was working properly exactly as it is, except without the ON UPDATE CASCADE ON DELETE CASCADE part. Only after adding the CASCADE parts did the error appear.

编辑2

为查明问题，以下是演示该错误的新测试代码:

In an attempt to pinpoint the problem, here is new test code that demonstrates the error:

此工作:

CREATE TABLE T1 (Id AUTOINCREMENT PRIMARY KEY);

CREATE TABLE T2 (
    Id AUTOINCREMENT PRIMARY KEY, 
    T1__Id int NOT NULL, 
    CONSTRAINT FK_T2_1 FOREIGN KEY (T1__Id) REFERENCES T1 (Id)
);

这给了错误:

CREATE TABLE T1 (Id AUTOINCREMENT PRIMARY KEY);

CREATE TABLE T2 (
    Id AUTOINCREMENT PRIMARY KEY, 
    T1__Id int NOT NULL, 
    CONSTRAINT FK_T2_1 FOREIGN KEY (T1__Id) REFERENCES T1 (Id) ON DELETE CASCADE
);

任何人都可以复制该错误吗?

Can anyone replicate the error?

推荐答案

您的CREATE语句是有效的Access DDL，但必须必须与ADO一起执行.

Your CREATE statement is valid Access DDL, but must be executed with ADO.

这里是一个即时窗口会话，演示了问题...

Here is an Immediate window session which demonstrates the problem ...

strSql = "CREATE TABLE T2 (" & vbCrLf & _
"    Id AUTOINCREMENT PRIMARY KEY, " & vbCrLf & _
"    T1__Id int NOT NULL, " & vbCrLf & _
"    CONSTRAINT FK_T2_1 FOREIGN KEY (T1__Id) REFERENCES T1 (Id) ON DELETE CASCADE" & vbCrLf & _
");"

' executing that statement with DAO triggers error 3289,
' "Syntax error in CONSTRAINT clause."
' (CurrentDb.Execute is a DAO Method)
CurrentDb.Execute strSql ' DAO -> fail

' CurrentProject.Connection.Execute is an ADO method,
' so this attempt executes without error
CurrentProject.Connection.Execute strSql ' ADO -> OK

请注意，如果您尝试从Access查询设计器中执行该语句，该语句也使用DAO，因此也会触发错误3289.

Note if you were trying to execute the statement from the Access query designer, that also uses DAO so would trigger error 3289 too.

这篇关于使用ON DELETE CASCADE定义表时的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！