agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRenumber Primary Keys and Update the same as Foreign Keys
4+ messages / 2 participants
[nested] [flat]
* Renumber Primary Keys and Update the same as Foreign Keys
@ 2015-06-10 23:05 Jon Forsyth <jon4syth@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jon Forsyth @ 2015-06-10 23:05 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks,
Jon
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Renumber Primary Keys and Update the same as Foreign Keys
@ 2015-06-10 23:51 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jon Forsyth <jon4syth@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2015-06-10 23:51 UTC (permalink / raw)
To: Jon Forsyth <jon4syth@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Renumber Primary Keys and Update the same as Foreign Keys
@ 2015-06-11 20:02 Jon Forsyth <jon4syth@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jon Forsyth @ 2015-06-11 20:02 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: 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.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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Renumber Primary Keys and Update the same as Foreign Keys
@ 2015-06-11 20:24 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jon Forsyth <jon4syth@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2015-06-11 20:24 UTC (permalink / raw)
To: Jon Forsyth <jon4syth@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-06-11 20:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-10 23:05 Renumber Primary Keys and Update the same as Foreign Keys Jon Forsyth <jon4syth@gmail.com>
2015-06-10 23:51 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-06-11 20:02 ` Jon Forsyth <jon4syth@gmail.com>
2015-06-11 20:24 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox