foreign key problem with my database

Question

I am creating a social networking site and am working on a friends table and dont know what Im doing wrong.

I have a users(userId, name - userId is primary key) and another table called friends(friend1, friend2, status). friend1 and friend2 are foreign keys to userId on update and delete are set to cascade table users has an entry with 134 in it as an Id and I want to change it to 3. I am just messing around to see how this stuff works. There are two rows with the value of 143 in the friends table. If I change 134 to 3 shouldnt it cascade on the update and also change those values in the friends table. I am getting this error

1451 - Cannot delete or update a parent row: a foreign key constraint fails (modionz1_nightspot/friends, CONSTRAINT friends_ibfk_1 FOREIGN KEY (friend1) REFERENCES users (userId))

both the tables are innodb by the way. Any help on these concepts would be greatly appreciated.

Answer 1

In the first place cascade update is a very bad idea on any database that expects to have more than a couple of hundred records. If you use it and you have thousands or millions of child records then you can lock up the entire system for hours. Avoid cascade update. The correct process is to add the parent record you want. Then update the child records to reflect that value instead of the intial one and then delte the old parent record. Hoever it is best to design where you don't ever need to update the PK which is one reason why many people use surrogate keys.