Hi,

I have a MySQL database with some foreign key contstraints.

The main table is Restaurant. It references the Type Of Restaurant table and the Price Range table with a foreign key constraints as follows:

alter table Restaurant
add (
constraint RESTAURANT_F2 FOREIGN KEY (PriceName) references PriceRange(Name) ON DELETE CASCADE ON UPDATE CASCADE,
constraint RESTAURANT_F1 FOREIGN KEY (TypeName) references TypeOfRestaurant(Name) ON DELETE CASCADE ON UPDATE CASCADE
);

When I try and update the TypeName of a TypeOfRestaurant or the Name of a Price Range, I get the following error:

"MySQL Error: Cannot delete a parent row: a foreign key constraint fails in Query"

The code I am using for the update is:

"UPDATE TypeOfRestaurant SET Name=\"$POST[Name]\" WHERE Name=\"$POST[OldName]\"

I thought the ON DELETE CASCASE ON UPDATE CASCADE should stop this happening (when a Name is changed, the effect should cascade) ? How can I get round this problem ?

Thanks,

Hal

    What is the table type? Mysql only supoprts FK for INNOdb table types, not MyISAM...

    Check yours...since you didn't mention it in the note

      Hi,

      They are InnoDB type (otherwise I wouldn't have been able to create the foreign keys in the first place I don't think ?). Anyway, I declared them to be InnoDB when they were created.

      Hal.

        Are you sure your version of MySQL has support for cascading fks? I'm pretty sure the early flavors supported the syntax (i.e. they'd swallow the command and pretend to create a cascading fk) but not the actual execution of cascading fks.

        Give me a database that errors out when it can't do what I tell it to, not one that says it's doing it while silently dropping the command because it can't handle it.

          OK, so if my version of mySQL does not support CASCADE, how can I update the parent table ?

          How can I update / delete from the child table at the same time as the update / delete from the parent table, so the foreign key remains valid ?

          Thanks,

          Hal

            You got me, I don't know. I use MySQL for content management type stuff. For anything with fks, stored procs, constraints, partial / functional indexes, and triggers I use Postgresql or firebird SQL.

            I would think that you might be able to use a NULL in that field temporarily, but I'm not sure.

              You'd mave to manage it with multiple mysql queries until each table was updated correctly...

                Write a Reply...