Обсуждение: 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
<div dir="ltr"><p>Thanks for the response. Here is the simplified table schema before the new 'question' table and mediatables are added:<br /><br />CREATE TABLE oral_question (<p> oral_question_id integer NOT NULL,<p> audio_prompt_file_pathcharacter varying(250) NOT NULL,<p> text_prompt text NOT NULL,<p>);<p>CREATE TABLE essay_question(<p> essay_question_id integer NOT NULL,<p> text_prompt text NOT NULL,<p><p>);<p>CREATE TABLE oral_question_response(<p> oral_question_response_id integer NOT NULL,<p> audio_response_file_path character varying(250)NOT NULL,<p> oral_question_id integer NOT NULL,<p>);<p>CREATE TABLE essay_question_response (<p> essay_question_response_idinteger NOT NULL,<p> response_text text NOT NULL,<p> essay_question_id integer NOT NULL,<p><p>); <p><br /><p>And after the 'question' table is added:<p><br /><p class=""><span class="">CREATE TABLE question(</span><p class=""><span class=""> question_id integer NOT NULL,</span><p><p class=""><span class="">);</span><pclass=""><span class=""><br /></span><p class=""><span class="">Then same as above except this new fieldis on the essay_question and oral_question tables:</span><p class=""><span class=""> </span><p class=""><span class="">question_idinteger NOT NULL,</span><p class=""><br />Thanks -Jon</div><div class="gmail_extra"><br /><div class="gmail_quote">OnWed, Jun 10, 2015 at 5:51 PM, Adrian Klaver <span dir="ltr"><<a href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">On06/10/2015 04:05 PM, Jon Forsyth wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"> Hello all,<br /><br /> I need to make a change to my schema such that theprimary key index<br /> numbers would change on multiple tables which are also used as foreign<br /> keys in multipletables. I want to update the foreign keys to the new<br /> primary key index number of each record. I would preferto do so using<br /> SQL statements.<br /><br /> My database is storing different kinds of questions in separate<br/> tables--1. 'essay_questions' and 2. 'oral_questions' (more question<br /> type tables are anticipated). Tosimplify relationships, I have created<br /> a parent table called 'questions' that will have a one-to-one<br /> relationshipwith each question type table using the same primary key on<br /> 'question' and 'essay_question' (same for 'question'and<br /> 'oral_question') for a given record. I will then associate different<br /> media items (videos, soundfiles, images) with the parent question table<br /> in a many-to-many relationship (many media items can belong to one<br/> question). As it stands, the different question tables have duplicate<br /> primary keys with respect to each other,so combining them into the<br /> parent question table will require a change to several or all primary<br /> keys. Additionally, I have live data where two tables 1.<br /> 'essay_question_response' and 2. 'oral_question_response'are associated<br /> in a many-to-many with their corresponding question tables which will<br />need the foreign keys updated after the change to primary keys.<br /><br /> Any suggestions?<br /></blockquote><br /></div></div>Post the actual schema definitions here, as I not entirely following the above. In the meantime you might tolook here:<br /><br /><a href="http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html" target="_blank">http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html</a><br/><br /> Search on REFERENCES.In particular ON UPDATE CASCADE.<br /><br /> Could be you already have the solution in place. Seeing the schemadefinitions would help us answer that.<br /><br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"><br /> Thanks,<br /><br /> Jon<span class="HOEnZb"><font color="#888888"><br/></font></span></blockquote><span class="HOEnZb"><font color="#888888"><br /><br /> -- <br /> AdrianKlaver<br /><a href="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a><br /></font></span></blockquote></div><br/></div>
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