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?
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?
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" ;-)
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: