CODEWITHSUNDEEP
postgresqlinheritanceforeign-keys
leetoff
leetoff

Reputation: 21

Postgres inheritance and foreign keys or alternative

I use postgres inheritance in my project.

For example: I have a "user" table and "user_child" that inherits from the "user" table.

I have two records: the first record is created in the user table, the second record is created in the user_child table, while the record from user_child is partially stored in user due to inheritance.

I also have a third table - "homework", it has a column assigned_user - a foreign key to the user table.

When I add an record to the "task" table where the "assigned_user" field refers to a record from the user table, then everything is fine, but when I select a record from the user_child table, I get an error:

ERROR: insert or update on table "homework" violates foreign key constraint "fk-homework-assigned_user""

DETAIL: Key (assigned_user)=(3) is not present in table "user".

Deleting a constraint helps solve my problem, but I want to use cascading deletion and updating records. Can you tell me what alternatives are there or what I'm doing wrong?

Upvotes: 2

Views: 1706

Answers (1)

Miles Elam
Miles Elam

Reputation: 1778

PostgreSQL inheritance doesn't quite work how you expect. Yes, you can see info from the child tables when querying the parent table, but this does not extend to foreign key relationships. The row "belongs" to the child, not the parent. The foreign key reference doesn't touch the child.

It's generally a bad idea to use inheritance in PostgreSQL except for specific cases like making a temporal system or enforcing naming conventions (like interfaces in OOP rather than state inheritance).

PostgreSQL inheritance can be very powerful, but it is generally overused in my opinion. There is already a solution (and cross-database compatible) that more closely follows the traditional relational model.

A better model that would do what you seem to want is the following:

CREATE TABLE "user" (
  user_id serial PRIMARY KEY, -- Or UUID or generated column in newer versions
  -- other fields that all "children" should share
);

CREATE TABLE user_child (
  user_id integer NOT NULL
    REFERENCES "user" (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
  -- other fields specific to the child
);

CREATE TABLE homework (
  homework_id serial PRIMARY KEY,
  user_id integer NOT NULL
    REFERENCES "user" (user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
  -- other fields specific to homework
);

The equivalent to your query for user_child is

SELECT u.user_id
FROM "user" AS u
  INNER JOIN user_child AS uc;

And to query user, both parent and child would still be

SELECT u.user_id
FROM "user" AS u;

Adding an inner join is pretty trivial and could be hidden behind a view. Now your foreign key reference to "user" will function correctly.

Upvotes: 5

Related Questions