agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Jon Forsyth <jon4syth@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Renumber Primary Keys and Update the same as Foreign Keys
Date: Thu, 11 Jun 2015 13:24:40 -0700
Message-ID: <5579EE88.3020507@aklaver.com> (raw)
In-Reply-To: <CACv13prfhN3jdEv1KHMhvnC7ubgAP+VH4LXCDPw8A3wCjvz4rw@mail.gmail.com>
References: <CACv13prL9UVoCqHexhUxs5ObRjW3v0F-ZaXbFmgQbAUUct=vkA@mail.gmail.com>
<5578CD85.2050901@aklaver.com>
<CACv13prfhN3jdEv1KHMhvnC7ubgAP+VH4LXCDPw8A3wCjvz4rw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (4+ messages)
Message-ID: <5579EE88.3020507@aklaver.com>
Permalink: ../5579EE88.3020507@aklaver.com/
Also on: postgresql.org/message-id/5579EE88.3020507@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, jon4syth@gmail.com
Subject: Re: Renumber Primary Keys and Update the same as Foreign Keys
In-Reply-To: <5579EE88.3020507@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox