Multiple foreign key constraints to distinct subsets of same relation

I have some xs. Every x either is or is not a y:

1. y ∪ not_y = x
2. y ∩ not_y = ∅

A y is a special x that can point to many other not_ys. A not_y cannot point to any x.

Is there a way to create a relation z(y, not_y) such that (y, not_y) is unique and the above constraints are enforced?

x and y could be modeled as two tables with a foreign key constraint, for instance:

create table x(a integer not null, primary key (a));
create table y(
    a integer not null,
    primary key (a),
    foreign key (a) references x (a)
);

y could also merely be a property of x. I have a lot of freedom in defining the model.

The naive take of

create table z(
    y_a integer not null,
    not_y_a integer not null,
    primary key (y_a, not_y_a),
    foreign key (y_a) references y (a),
    foreign key (not_y_a) references x (a)
);

prevents duplicates and enforces the integrity of y_a (provided y retains integrity but we can afford that assumption) but doesn’t enforce the second constraint. I could make a moral opposite of the y table:

create table not_y(
    a integer not null,
    primary key (a),
    foreign key (a) references x (a)
);

Then use that for the not_y_a foreign key constraint, extending the same assumptions to the not_y table as to the y table. However, then I have double-bookkeeping of y-ness, which I suspect is more costly to me than tolerating the limitations of the z table.

Another way to think of it is to say that I’m looking for a negative foreign key constraint.

SQL Fiddle for consideration.

I’m using PostgreSQL 10.

Answer

CREATE TABLE x (
   id bigint PRIMARY KEY,
   is_y boolean NOT NULL,
   UNIQUE (id, is_y)
);

CREATE VIEW y AS
SELECT id FROM x WHERE is_y;

CREATE VIEW not_y AS
SELECT id FROM x WHERE NOT is_y;

CREATE TABLE z (
   y_id bigint NOT NULL,
   y_is_y boolean NOT NULL DEFAULT TRUE
      CHECK (y_is_y),
   not_y_id bigint NOT NULL,
   not_y_is_y boolean NOT NULL DEFAULT FALSE
      CHECK (NOT not_y_is_y),
   PRIMARY KEY (y_id, not_y_id),
   FOREIGN KEY (y_id, y_is_y) REFERENCES x (id, is_y),
   FOREIGN KEY (not_y_id, not_y_is_y) REFERENCES x (id, is_y
);

This requires an explanation:

• x, y and not_y really are the same table. is_y determines if a row belongs to y or to not_y. The partition is automatically disjoint, and it is covering because is_y is not NULL.

• The unique constraint on x is needed as a target for foreign keys.

• The two boolean columns in z are implementation artifacts and are always constant. They are needed for the foreign keys, so that a y_id is guaranteed to point to a y.

• Uniqueness of the mapping in z is guaranteed by the primary key.