Ensure there's exactly one foreign key reference from a set of tables

Question

I'm trying to design a database structure that allows me to extract common fields into one table called "entity".

You could think of "entity" as an abstract class. Every "entity" has an owner (a referenced user-account), a creation-time and some key-value tags.

Every "entity" is actually either an "object" or an "view". Never both. Never none of them.

Is this constraint possible to enforce on an PostgreSQL Database (latest version)? If not, tell me and feel free to suggest changes for my current schema.

My initialization SQL looks like this:

CREATE TABLE "account" (
    "id" BIGSERIAL NOT NULL,
    "issuer" VARCHAR NOT NULL,
    "name" VARCHAR NOT NULL,
    PRIMARY KEY ("id"),
    UNIQUE ("issuer", "name")
) ;

CREATE TABLE "entity" (
    "id" BIGSERIAL NOT NULL,
    "owner_account_id" BIGINT NOT NULL,
    "creation_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("owner_account_id") REFERENCES "account" ("id") ON DELETE CASCADE ON UPDATE CASCADE
) ;

CREATE TABLE "entity_tag" (
    "entity_id" BIGINT NOT NULL,
    "key" VARCHAR(100) NOT NULL,
    "value" VARCHAR(1000) NOT NULL,
    PRIMARY KEY ("entity_id", "key"),
    FOREIGN KEY ("entity_id") REFERENCES "entity" ("id") ON DELETE CASCADE ON UPDATE CASCADE
) ;

CREATE TABLE "object" (
    "id" BIGSERIAL NOT NULL,
    "entity_id" BIGINT NOT NULL,
    "mime_type" VARCHAR NOT NULL,
    "size" BIGINT NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("entity_id") REFERENCES "entity" ("id") ON DELETE CASCADE ON UPDATE CASCADE
) ;

CREATE TABLE "view" (
    "id" BIGSERIAL NOT NULL,
    "entity_id" BIGINT NOT NULL,
    "view_expression" JSON NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("entity_id") REFERENCES "entity" ("id") ON DELETE CASCADE ON UPDATE CASCADE
) ;

(Sure, I can and I actually currently do enforce this in my application. But if there's a way to enforce this on the Database too, I would like to do it)

You are lucky. This can be done in PostgreSQL or Oracle. – The Impaler Jan 08 '21 at 00:07 — The Impaler, Jan 08 '21 at 00:07

score 1 · Answer 1 · answered Jan 07 '21 at 23:49

there is no simple way, you must add a column to indicate the type of the entity in the entity table:

CREATE TYPE type_entity_class AS ENUM ('entity_tag', 'object', 'view');

add a column in the entity table: entity_class type_entity_class,

in entity_tag table add column: entity_class type_entity_class CHECK (entity_class = 'entity_tag')

in object table add column: entity_class type_entity_class CHECK (entity_class = 'object') ..

change:

FOREIGN KEY ("entity_id","entity_class") REFERENCES "entity" ("id","entity_class")

Thats a nice idea! But this only enforces 0-1 and not 1-1 if I'm correct? Still, better than what I currently have! — Felix, Jan 08 '21 at 00:22

score 1 · Accepted Answer · answered Jan 08 '21 at 00:37

You can do this in PostgreSQL (how awesome PostgreSQL is) and also in Oracle, since you need an engine that supports deferrable constraints, an integral part of the SQL Standard.

You can do:

create table entity (
  id int primary key not null,
  name varchar(20) not null,
  object_id int,
  view_id int,
  check (object_id is null and view_id is not null
      or object_id is not null and view_id is null)
);

create table object (
  id int primary key not null references entity (id),
  mime_type varchar(20) not null
);

create table view (
  id int primary key not null references entity (id),
  view_expression varchar(50) not null
);

alter table entity add constraint c1
foreign key (object_id) references object (id) deferrable initially deferred;

alter table entity add constraint c2
foreign key (view_id) references view (id) deferrable initially deferred;

Now, you can insert an object and a view:

begin transaction;
insert into entity (id, name, object_id, view_id)
            values (10, 'Object-10', 10, null);
insert into object (id, mime_type) values (10, 'image/png');
commit;

begin transaction;
insert into entity (id, name, object_id, view_id) 
            values (12, 'View-12', null, 12);
insert into view (id, view_expression) values (12, 'a+b*c');
commit;

But you cannot insert an abstract entity (no concrete row):

begin transaction;
insert into entity (id, name, object_id, view_id)
            values (14, 'no-type-14', null, null);
commit; -- fails!

Neither can you insert an entity which is both an object and a view:

begin transaction;
insert into entity (id, name, object_id, view_id) values (16, 'Dual-16', 16, 16);
insert into object (id, mime_type) values (16, 'text/plain');
insert into view (id, view_expression) values (16, 'x*x');
commit; -- fails!

Remember the insertion of rows needs to be enclosed in a transaction to defer the constraint checks until the end of the transaction.

See running example at DB Fiddle.

Thats perfect! Can the check also check if the not-nulled id is equal to the actual id or can it only do null / not null checks? — Felix, Jan 08 '21 at 01:11

Ensure there's exactly one foreign key reference from a set of tables

2 Answers2