Re: Renumber Primary Keys and Update the same as Foreign Keys - Mailing list pgsql-sql

<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>