Message-ID: <5578CD85.2050901@aklaver.com>
Date: Wed, 10 Jun 2015 16:51:33 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: Jon Forsyth <jon4syth@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: Renumber Primary Keys and Update the same as Foreign Keys
References: 
 <CACv13prL9UVoCqHexhUxs5ObRjW3v0F-ZaXbFmgQbAUUct=vkA@mail.gmail.com>
In-Reply-To: 
 <CACv13prL9UVoCqHexhUxs5ObRjW3v0F-ZaXbFmgQbAUUct=vkA@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/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


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