PostgreSQL Foreign Key and Child Tables

Question

I have the following tables:

--Competition tables
CREATE TABLE IF NOT EXISTS Tr.Competitions(
    competition_id      SERIAL PRIMARY KEY,
    competition_name    text NOT NULL
);

CREATE TABLE IF NOT EXISTS Tr.CompetitionsQuestions(
    competition_id      int NOT NULL,
    question_id         int NOT NULL,
    FOREIGN KEY (competition_id) REFERENCES Tr.Competitions(competition_id),
    FOREIGN KEY (question_id) REFERENCES Tr.Questions(question_id)
);

--Questions tables
CREATE TABLE IF NOT EXISTS Tr.Questions(
    question_id         SERIAL PRIMARY KEY,
    question_text       text NOT NULL
);

CREATE TABLE IF NOT EXISTS Tr.MultiQuestions(
    possible_answers    text ARRAY NOT NULL,
    correct_answer      int NOT NULL
) INHERITS(Tr.Questions);

I try to insert some dummy data into Tr.CompetitionQuestions like so:

--Test Fixtures
INSERT INTO Tr.MultiQuestions (question_text, possible_answers, correct_answer) 
    VALUES ('Which of the following is awesome?', '{"Indian Food","Soccer","All the above"}', 2);

INSERT INTO Tr.Competitions(competition_name)
    VALUES ('Awesome Competition');

INSERT INTO Tr.CompetitionsQuestions(competition_id, question_id) 
    VALUES ((SELECT competition_id FROM Tr.Competitions WHERE competition_id=1),
            (SELECT question_id FROM Tr.Questions WHERE question_id=1));

Having these stored in an .sql file and running \i some.sql is gerenating the following error. How do I add a question foreign key to the CompetitionsQuestions table?

 ERROR:  insert or update on table "competitionsquestions" violates foreign key constraint "competitionsquestions_question_id_fkey"      
DETAIL:  Key (question_id)=(1) is not present in table "questions".

Seems like a weird error since SELECT * FROM tr.questions WHERE question_id=1 actually gives me the stored multiquestion row.

EDIT:

Simplifying to:

INSERT INTO Tr.CompetitionsQuestions(competition_id, question_id) 
    VALUES (1, 1);

gives me the same error;

I insert into Tr.MultiQuestions which inherits from Tr.Questions. There are other questions types (not shown) such as Tr.TrueFalse Tr.ShortAnswer which also inherit from Tr.Questions — moesef
– moesef, Commented Jul 19, 2014 at 6:24
My intention was to allow the use any type of Question as the FK in the CompetitionsQuestions table — moesef
– moesef, Commented Jul 19, 2014 at 6:32
select * from questions is equivalent to select * from questions* and selects from questions and all its children, which is why you're seeing question_id 1. select * from only questions will probably not return any results, which is where postgres is trying to satisfy the fk requirement. you may need to use a 3rd table and triggers to achieve what you're trying to do. — FuzzyTree
– FuzzyTree, Commented Jul 19, 2014 at 6:45

Craig Ringer · Accepted Answer · 2014-07-19 13:02:00Z

1

(Assuming, from comments, that you're using PostgreSQL's table inheritance features, since your question doesn't really contain full info about the schema and how you populate its contents):

Foreign keys don't apply across all members of an inheritance tree. They can only be to the specific table.

The same is true of a UNIQUE constraint or PRIMARY KEY.

You can see what a foreign key constraint will see in a table if you:

 SELECT * FROM ONLY thetable;

The ONLY keyword tells PostgreSQL not to include child tables. That's what's used in foreign key constraint checks.

answered Jul 19, 2014 at 13:02

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Foreign Key and Child Tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related