CREATE TABLE/foreign key problem 1

[jwigal]

I am trying to create via a CREATE TABLE command (CurrentDB.Execute sql) a single table that links a "referee" from one table with a "team" from a team table.

I know how to do this manually via the Table design view, but I need to do this programmatically via VB code.

Table "Referee Data"
Primary Key Field Name: "ID"

Table "Teams"
Primary Key Field Name: "ID"

Here is the basic command that works correctly:

CREATE TABLE TeamConflict2 ( 
 RefereeID Long NOT NULL 
 CONSTRAINT FKReferee
 REFERENCES [Referee Data] (ID),

 TeamID Long NOT NULL
 CONSTRAINT FKTeam
 REFERENCES [Teams] (ID),

 CONSTRAINT RefTeam
 PRIMARY KEY (RefereeID, TeamID)
);

I am trying to set this up so that if the referee's record, from the table "Referee Data" is deleted, the corresponding records in TeamConflict2 are also deleted.

Also, if the team's record, from the table "Teams" is deleted, the corresponding records in TeamConflict2 are also deleted.

I have tried each of the following:

CREATE TABLE TeamConflict2 ( 
 RefereeID Long NOT NULL 
 CONSTRAINT FKReferee
 REFERENCES [Referee Data] (ID)
 ON DELETE CASCADE,

 TeamID Long NOT NULL
 CONSTRAINT FKTeam
 REFERENCES [Teams] (ID)
 ON DELETE CASCADE,

 CONSTRAINT RefTeam
 PRIMARY KEY (RefereeID, TeamID)
);

and

CREATE TABLE TeamConflict2 ( 
 RefereeID Long NOT NULL 
 CONSTRAINT FKReferee
 REFERENCES [Referee Data] (ID),

 TeamID Long NOT NULL
 CONSTRAINT FKTeam
 REFERENCES [Teams] (ID),

 CONSTRAINT RefTeam
 PRIMARY KEY (RefereeID, TeamID)
 ON DELETE CASCADE
);

Each time I get a "syntax error in CONSTRAINT clause".

Any thoughts? What am I missing here?

----------
Jeff Wigal
Referee Assistant for MS Access

http://www.referee-assistant.com

 

[rac2]

It may be that Jet SQL does not support the ON DELETE CASCADE option.

I got this reference manual using google search on

jet sql reference manual

http://www.devguru.com/Technologies/jetsql/quickref/constraint.html

and

http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html

Just an idea. There may be more to it if you are using DAO.

 

[jwigal]

I didn't take a look at the links, but I tried the same with an ADO syntax, e.g.:

CurrentProject.Connection.Execute sql

with my "example #1" defined as the string sql, and it worked right away.

Thanks!

----------
Jeff Wigal
Referee Assistant for MS Access

http://www.referee-assistant.com