I can't figure out how to specify an 'on delete cascade'. Here are my table defs. As is, I can not delete a record from the categories table because I get an error message 'The record cannot be deleted or changed because table 'Types' includes related records.' My intention is for the records in the 'Types' table to be cascade deleted.
I have tried adding the phrase 'on delete cascade' during the table creation and I get an error 'Syntax error in CONSTRAINT clause.' Any help would be great. Thanks
Create table Categories
(cid text(8) unique not null,
Category text(80) Not Null,
Face integer not null,
CONSTRAINT PK_Cid Primary Key (cid))
Are you doing this in Access or SQL Server? I just want to confirm because you would take a different approach for each.
I've had problems with relying on relationships in Access to use referential integrity for cascading deletes. If I wanted to delete a parent record, then I had to delete all dependent (child) records first in a separate process.
For SQL Server:
Create a trigger to delete the corresponding child records. That works like a charm.
I am doing this to an Access data base through a Cold Fusion page issuing SQL commands. I don't have Access available to me (nor would it help if I did). All changes to the database on my web server are done with SQL calls. All the documentation I find says that I can issue the 'on delete cascade' but it doesn't work.
I was hoping not to have to write the children delete routines but it looks like I may have to. Thanks for your help.