From: | Jon Forsyth <jon4syth(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Renumber Primary Keys and Update the same as Foreign Keys |
Date: | 2015-06-11 20:02:19 |
Message-ID: | CACv13prfhN3jdEv1KHMhvnC7ubgAP+VH4LXCDPw8A3wCjvz4rw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-06-11 20:24:40 | Re: Renumber Primary Keys and Update the same as Foreign Keys |
Previous Message | Adrian Klaver | 2015-06-10 23:51:33 | Re: Renumber Primary Keys and Update the same as Foreign Keys |