CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
mkjeldsen
mkjeldsen

Reputation: 2180

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 ya (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.

Upvotes: 1

Views: 111

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246268

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I think you can do:

create table x (
    x_id int not null primary key,
    y_id int unique
);

create table y (
    x_id not null primary key
    constraint fk_y_x_id foreign key (x_id) references y(x_id)
);

alter table x add constraint fk_x_y foreign key (y_id) references y(y_id);

The two unique constraints would seem to do what you want. However, it is a little tricky to insert data into the above data model.

You might also want to look into table inheritance. It might be a more direct way to do what you want.

Upvotes: 0

Related Questions