[Prodoc]

Is it possible to change all foreign keys, in one go, which point to a specific primary key and make them point to a different primary key?

Example:
Person(person_id, name, dob, ...)
Class(class_id, teacher_id, name, start_date, ...)
Bus(bus_id, driver_id, name, mileage, ...)

Where teacher_id and driver_id contain a primary key person_id from Person.

Suppose one and the same person is a teacher for a class and a driver for a bus, a new person comes in and takes over both roles of the previous person.
What I would like to do is to change all foreign keys in every available table which point to the first person_id and make them point to the new person_id without having to specify this for each individual table. Is this possible?

I'm using MySQL 5.0 with InnoDB tables.

[r937]

make sure you declare your foreign keys with ON UPDATE CASCADE

that's exactly what this feature was designed for



p.s. ordinarily you would never update a person to change that person into a new person -- instead, it should be a deletion followed by an insertion

[Prodoc]

I'm not sure I understand you correctly.

ON UPDATE CASCADE goes into effect when you change the primary key of the first person in the Person table. This, however, isn't the case. I'm adding a new person which gets a new primary key and the old person remains present. Knowing this, I don't quite see how ON UPDATE CASCADE would help in my situation. If I change the primary key of the first person, all foreign keys would change to the new primary key as well so the situation remains the same.
As far as I know I can't change the primary key of the new person to the primary key of the first person, followed by assigning a new primary key to the first person. This would cause you to have two identical primary keys in one table after the first step, which isn't allowed.

In addition, is it a desired effect to change the primary key of the first person in the first place?

[r937]

you have an excellent grasp of the situation

the only way to get the cascade to work is by updating the existing primary key

whether you would really want to do that is questionable

sorry if i didn't make that too clear

[Prodoc]

Correct me if I'm wrong but as far as I can tell this basically comes down to keeping track of all tables where a person has a foreign key and change the foreign key, with the newly created primary key, in each table one by one. Therefore the answer on my initial question would be "no, it's not possible" ;-)

[r937]

well, you could do it like this

1. change the first person's row (including the primary key) so that it now reflects the new person completely -- this would cascade the PK to all the related tables

2. add the first person back with her original PK

[Prodoc]

Originally Posted by r937

well, you could do it like this

1. change the first person's row (including the primary key) so that it now reflects the new person completely -- this would cascade the PK to all the related tables

2. add the first person back with her original PK

Indeed, you're right! I didn't think about that one, thanks.

Is it also possible to introduce some restrictions on the ON UPDATE CASCADE?
Suppose I want to change all foreign keys of the first person to a new person but only for a specific school_id.
To change the original table structure a bit:

Person(person_id, name, dob, ...)
Class(school_id, class_id, teacher_id, name, start_date, ...)
Bus(school_id, bus_id, driver_id, name, mileage, ...)

Now with an additional school_id in the primary key of Class and Bus.

[r937]

nope, sorry -- foreign key cascades operate on the foreign key regardless of what other columns are involved in the tables