Hello all,
I need to make a change to my schema such that the primary key index numbers would change on multiple tables which are also used as foreign keys in multiple tables. I want to update the foreign keys to the new primary key index number of each record. I would prefer to do so using SQL statements.
My database is storing different kinds of questions in separate tables--1. 'essay_questions' and 2. 'oral_questions' (more question type tables are anticipated). To simplify relationships, I have created a parent table called 'questions' that will have a one-to-one relationship with each question type table using the same primary key on 'question' and 'essay_question' (same for 'question' and 'oral_question') for a given record. I will then associate different media items (videos, sound files, images) with the parent question table in a many-to-many relationship (many media items can belong to one question). As it stands, the different question tables have duplicate primary keys with respect to each other, so combining them into the parent question table will require a change to several or all primary keys. Additionally, I have live data where two tables 1. 'essay_question_response' and 2. 'oral_question_response' are associated in a many-to-many with their corresponding question tables which will need the foreign keys updated after the change to primary keys.
Any suggestions?
Thanks,
Jon