Can i have one foreign key to both parent and it's childs in postgresql?

Question

I want to have a foreign key to reference table and it's children. Here's how I tried and failed:

CREATE TABLE parent (
 id serial,
 CONSTRAINT parent_pk PRIMARY KEY(id)
);

CREATE TABLE child (
  val text,
  constraint child_pk PRIMARY KEY(id)
) INHERITS(parent);

CREATE TABLE test (
  id serial,
  foreign_id bigint REFERENCES parent(id),
  constraint testpk PRIMARY KEY (id)
)

Lets say we have such data:

parent:
|id| 
+--+
|1 | --public.parent oid
|2 | --public.child oid

child:
|id|val|
+--+---+
|2 |'a'| --public.child oid

what i whant to acheive is this:

test:
|id|foreign_id|
+--+----------+
|1 | 1        | --(from public.parent table)
|2 | 2        | --(from public.child table)

is there a good workaround here?

Answer 1

this is not possible out of the box. inheritance in PostgreSQL is based on individual tables, which are only logically connected through pg_inherit. Each table is still somewhat independent. Therefore you cannot create a key on the entire structure. It is only possible to reference individual tables. What you can do, however, is to write a trigger ensuring integrity. A foreign key is basically the same behind the scenes anyway.

Answer 2

What you also could do is to set up a table where only the IDs are stored:

CREATE TABLE common (id serial PRIMARY KEY);

Then change both your parent and child so that their primary keys are also foreign keys referring to this table:

CREATE TABLE parent (
    id integer PRIMARY KEY REFERENCES common (id),
    ...
);

Similarly, any table referring to both parent and child, should have a reference to common instead.

Of course, this means you have to change the logic how you insert data into parent and child. This means an additional INSERT - it can be a part of the function used for insertion, can be a trigger procedure or a plain INSERT in your application code, all depending on your existing approach.

Answer 3

I have found a soultion with a help from this answer Can i have one foreign key to both parent and it's childs in postgresql? , here's a trigger:

CREATE TABLE test (
  id serial,
  foreign_id bigint NOT NULL,
  constraint testpk PRIMARY KEY (id)
)


CREATE OR REPLACE FUNCTION inheritance_constraint()
  RETURNS trigger AS
$BODY$
  DECLARE
    refererColumn text;
    refererId bigint;
    parentTable text;
    parentId text;
    sqlStatement text;
    result boolean;
  BEGIN
    --check if trigger is applied for each row
    IF TG_LEVEL != 'ROW' THEN
      RAISE EXCEPTION 'Triger procedure "inheritance_constraint" must be applied for each row';
    END IF;
    --check if at least two arguments are passed
    IF TG_NARGS < 3 THEN
      RAISE EXCEPTION 'Triger procedure "inheritance_constraint" needs at least three arguments (% received)',TG_NARGS;
    END IF;

    refererColumn = TG_ARGV[0];
    --Pick the id
    EXECUTE 'SELECT (($1).' || refererColumn || ')::bigint ;' INTO refererId USING NEW;
    --check if argument exists
    IF refererId = NULL THEN
      RAISE EXCEPTION 'Column % does not exists',refererColumn;
    END IF;
    --get parent table
    parentTable = TG_ARGV[1];

    --get parent table id
    parentId = TG_ARGV[2];

    --build a query
    sqlStatement = format('SELECT EXISTS(SELECT 1 FROM %s ', parentTable );

    --if any child tables are specified in arguments, build a checking query
    IF (TG_NARGS > 3) THEN
      sqlStatement = sqlStatement || format(' JOIN pg_class ON %s.tableoid = pg_class.oid WHERE pg_class.relname IN(',parentTable);
      FOR i IN 3..(TG_NARGS-1) LOOP
        sqlStatement = sqlStatement || '''' || TG_ARGV[i] || ''''; 
        IF i != (TG_NARGS-1) LOOP THEN
          sqlStatement = sqlStatement || ',';
        END IF;
      END LOOP;
      sqlStatement = sqlStatement || ') AND';
    ELSE 
      sqlStatement = sqlStatement || ' WHERE '; 
    END IF;
    sqlStatement = sqlStatement || format(' %s = %s LIMIT 1 )::boolean;',parentId,refererId);

    --query is built

    --Now check if id exists
    EXECUTE sqlStatement INTO result;
    IF result != TRUE  THEN 
      RAISE EXCEPTION 'Related id % does not exist',refererId;
    END IF;

    RETURN NEW;
  END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER test_references_parent 
  BEFORE UPDATE OF foreign_id OR INSERT ON test
  FOR EACH ROW 
  EXECUTE PROCEDURE inheritance_constraint('foreign_id','parent','parent.id');

I agree, it's ugly. Hoewer this doc page states

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 release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.

So we might not need such ugly workaround in the future

UPDATE

There is another workaround worth a thought- it's using views

CREATE TABLE all_in_one (
  id serial,
  val text,
  type text -- defines whitch class the row belongs to
);

CREATE VIEW parent_view AS SELECT id FROM all_in_one;
CREATE VIEW child_view AS SELECT id, val FROM all_in_one WHERE type = 'child';

With some tinkering you can even make inserts into views, so it would look like youre working with inheritance, but everything would be in a single table.