Skip site navigation (1) Skip section navigation (2)

Foreign key constraint violation when using table inheritance

From: Doug Wiebe <dwiebe(at)me(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Foreign key constraint violation when using table inheritance
Date: 2011-11-02 22:33:54
Message-ID: 7D346CD7-FD4B-49D6-9764-ABD160313735@me.com (view raw or flat)
Thread:
Lists: pgsql-novice
My TestDB has the following schema:

    CREATE TABLE name (
        name_id    BigSerial PRIMARY KEY,
        name       Text     
    );

    CREATE TABLE base_value (
        value_id    BigSerial PRIMARY KEY
    );

    CREATE TABLE string_value (
        value       Text
    ) INHERITS (base_value);

    CREATE TABLE name_value (
        name_id     BigInt REFERENCES name,
        value_id    BigInt REFERENCES base_value
    );

The following inserts result in a failure:

    TestDB=# INSERT INTO name (name) VALUES ('a name');
    INSERT 0 1

    TestDB=# INSERT INTO string_value (value) VALUES ('a string');
    INSERT 0 1

    TestDB=# INSERT INTO name_value (name_id, value_id) VALUES (1, 1);
    ERROR:  insert or update on table "name_value" violates foreign key constraint "name_value_value_id_fkey"
    DETAIL:  Key (value_id)=(1) is not present in table "base_value".

even though both base_value and string_value have rows with value_id 1:

    TestDB=# SELECT * FROM base_value;
     value_id 
    ----------
            1
    (1 row)

    TestDB=# SELECT * FROM string_value;
     value_id |  value   
    ----------+----------
            1 | a string
    (1 row)

What am I doing wrong?

- Doug


Responses

pgsql-novice by date

Next:From: felixDate: 2011-11-02 22:57:05
Subject: Re: DBI, savepoints, transactions, and AutoCommit
Previous:From: Daniel StaalDate: 2011-11-02 22:05:56
Subject: Re: DBI, savepoints, transactions, and AutoCommit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group