Re: Renumber Primary Keys and Update the same as Foreign Keys - Mailing list pgsql-sql
From | Adrian Klaver |
---|---|
Subject | Re: Renumber Primary Keys and Update the same as Foreign Keys |
Date | |
Msg-id | 5579EE88.3020507@aklaver.com Whole thread Raw |
In response to | Re: Renumber Primary Keys and Update the same as Foreign Keys (Jon Forsyth <jon4syth@gmail.com>) |
List | 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