Thread: Foreign key constraint violation when using table inheritance

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

Doug Wiebe <dwiebe@me.com> wrote:

> [...]
> What am I doing wrong?

<URI:http://www.postgresql.org/docs/current/static/ddl-inherit.html>:

| [...]

| 5.8.1. Caveats

| [...]

|   A serious limitation of the inheritance feature is that
| indexes (including unique constraints) and foreign key con-
| straints only apply to single tables, not to their inheri-
| tance children.  This is true on both the referencing and
| referenced sides of a foreign key constraint.  Thus, in the
| terms of the above example:

| [...]

| * Specifying that another table's column REFERENCES
|   cities(name) would allow the other table to contain city
|   names, but not capital names.  There is no good workaround
|   for this case.

| These deficiencies will probably be fixed in some future re-
| lease, but in the meantime considerable care is needed in
| deciding whether inheritance is useful for your application.

Tim