Message-ID: <5579EE88.3020507@aklaver.com>
Date: Thu, 11 Jun 2015 13:24:40 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: Jon Forsyth <jon4syth@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Renumber Primary Keys and Update the same as Foreign Keys
References: 
 <CACv13prL9UVoCqHexhUxs5ObRjW3v0F-ZaXbFmgQbAUUct=vkA@mail.gmail.com>
	<5578CD85.2050901@aklaver.com>
 <CACv13prfhN3jdEv1KHMhvnC7ubgAP+VH4LXCDPw8A3wCjvz4rw@mail.gmail.com>
In-Reply-To: 
 <CACv13prfhN3jdEv1KHMhvnC7ubgAP+VH4LXCDPw8A3wCjvz4rw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql