Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003

    Unanswered: Cascading delete

    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))

    Create table Types
    (tid counter unique not null,
    cid text(8) not null,
    Type1 text(50),
    Type2 text(50),
    Type3 text(50),
    CONSTRAINT FK_Cid Foreign Key (cid) References Categories (cid))

  2. #2
    Join Date
    Apr 2003
    Evansville, Indiana


    Are you doing this in Access or SQL Server? I just want to confirm because you would take a different approach for each.

    For Access:
    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.



  3. #3
    Join Date
    Apr 2003

    Cascading Delete

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts