ON UPDATE CASCADE problem

[tofuwarrior]

Hi All,

 

This is my first time with foreign keys and I can see they are amazing but this has me stumped.

 

Any help, really gratefully received,

 

I have 2 tables

 

cms_objects

cms_users.

 

Each has an id column.

 

I have set up a foreign key on cms_users.id referencing cms_objects.id with ON DELETE CASCADE ON UPDATE CASCADE

 

When I delete from cms_objects it cascades fine and deletes the relevant cms_users record.

 

BUT when I for example try to update a row in cms_objects which has id =1(exists in cms_users) to be id =15 the change does not cascade changing the cms_users id 1 to be 15.

 

Am I missing something obvious?

 

Thanks,

 

TW

[fenway]

I would ask my you're chaning the UID value... but I can't explain it.

[Simon Moon]

My guess is you try to change an id on something and then it tries to update the id on the other table, but there the id is a unique value field and most likely also auto_increment, which will wreak havoc. MySQL knows that and does not allow it by default. Look at the manual around for the cascade descriptions, i thought i saw it mentioned there.

[fenway]

Probably something like that, the FK constraint is by UID, so if you change the uid, the "child" record isn't really linked anymore.