Request a Demo
Home   >   mailing lists

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

From Jon Forsyth
Subject Re: Renumber Primary Keys and Update the same as Foreign Keys
Date
Msg-id CACv13prfhN3jdEv1KHMhvnC7ubgAP+VH4LXCDPw8A3wCjvz4rw@mail.gmail.com
Whole thread
In response to Re: Renumber Primary Keys and Update the same as Foreign Keys  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Renumber Primary Keys and Update the same as Foreign Keys
List pgsql-sql
Tree view

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

pgsql-sql by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Renumber Primary Keys and Update the same as Foreign Keys
Next
From: Adrian Klaver
Date:
Subject: Re: Renumber Primary Keys and Update the same as Foreign Keys