Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Unanswered: Change all foreign keys

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    13
    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?
    Last edited by Prodoc; 10-23-07 at 09:40.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    13
    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" ;-)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    13
    Quote 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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, sorry -- foreign key cascades operate on the foreign key regardless of what other columns are involved in the tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •