Обсуждение: Renumber Primary Keys and Update the same as Foreign Keys
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?
Any suggestions?
Thanks,
Jon
On 06/10/2015 04:05 PM, Jon Forsyth wrote: > 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? Post the actual schema definitions here, as I not entirely following the above. In the meantime you might to look here: http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html Search on REFERENCES. In particular ON UPDATE CASCADE. Could be you already have the solution in place. Seeing the schema definitions would help us answer that. > > Thanks, > > Jon -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for the response. Here is the simplified table schema before the new 'question' table and media tables are added:
CREATE TABLE oral_question (
oral_question_id integer NOT NULL,
audio_prompt_file_path character varying(250) NOT NULL,
text_prompt text NOT NULL,
);
CREATE TABLE essay_question (
essay_question_id integer NOT NULL,
text_prompt text NOT NULL,
);
CREATE TABLE oral_question_response (
oral_question_response_id integer NOT NULL,
audio_response_file_path character varying(250) NOT NULL,
oral_question_id integer NOT NULL,
);
CREATE TABLE essay_question_response (
essay_question_response_id integer NOT NULL,
response_text text NOT NULL,
essay_question_id integer NOT NULL,
);
And after the 'question' table is added:
CREATE TABLE question (
question_id integer NOT NULL,
);
Then same as above except this new field is on the essay_question and oral_question tables:
question_id integer NOT NULL,
Thanks -Jon
On Wed, Jun 10, 2015 at 5:51 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Post the actual schema definitions here, as I not entirely following the above. In the meantime you might to look here:On 06/10/2015 04:05 PM, Jon Forsyth wrote: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?
http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html
Search on REFERENCES. In particular ON UPDATE CASCADE.
Could be you already have the solution in place. Seeing the schema definitions would help us answer that.
Thanks,
Jon
--
Adrian Klaver
adrian.klaver@aklaver.com
On 06/11/2015 01:02 PM, Jon Forsyth wrote: > Thanks for the response. Here is the simplified table schema before the > new 'question' table and media tables are added: > > CREATE TABLE oral_question ( > > oral_question_id integer NOT NULL, > > audio_prompt_file_path character varying(250) NOT NULL, > > text_prompt text NOT NULL, > > ); > > CREATE TABLE essay_question ( > > essay_question_id integer NOT NULL, > > text_prompt text NOT NULL, > > ); > > CREATE TABLE oral_question_response ( > > oral_question_response_id integer NOT NULL, > > audio_response_file_path character varying(250) NOT NULL, > > oral_question_id integer NOT NULL, > > ); > > CREATE TABLE essay_question_response ( > > essay_question_response_id integer NOT NULL, > > response_text text NOT NULL, > > essay_question_id integer NOT NULL, > > ); > > > And after the 'question' table is added: > > > CREATE TABLE question ( > > question_id integer NOT NULL, > > ); > > > Then same as above except this new field is on the essay_question and > oral_question tables: > > question_id integer NOT NULL, I am not seeing the PRIMARY KEYS on the above or even a UNIQUE index, so are the duplicates within the table or between the tables? Assuming the parent table is question and the childs are essay_question and oral_question the question_id could be added to each as FK that points back to question. What I cannot see from here is how you know which essay_question and oral_question point to the same question? > > > Thanks -Jon > > > On Wed, Jun 10, 2015 at 5:51 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 06/10/2015 04:05 PM, Jon Forsyth wrote: > > 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? > > > Post the actual schema definitions here, as I not entirely following > the above. In the meantime you might to look here: > > http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html > > Search on REFERENCES. In particular ON UPDATE CASCADE. > > Could be you already have the solution in place. Seeing the schema > definitions would help us answer that. > > > Thanks, > > Jon > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com