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)

Answer 1

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.