CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-designconstraints
Niek Tax
Niek Tax

Reputation: 841

Many-to-many relationship with non-zero cardinality

Let's say I have a PostgreSQL database with tables A, B and C, where A and B have a many-to-many relationship via junction table C. These tables have the following SQL definitions:

CREATE TABLE A
( 
  id serial NOT NULL,
  CONSTRAINT A_pkey PRIMARY KEY (id)
)

CREATE TABLE B
(
  id serial NOT NULL,
  CONSTRAINT B_pkey PRIMARY KEY (id)
)

CREATE TABLE C
(
  A_id integer NOT NULL,
  B_id integer NOT NULL,
  CONSTRAINT C_pk PRIMARY KEY (A_id, B_id),
  CONSTRAINT A_fk FOREIGN KEY (A_id)
      REFERENCES A(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT B_fk FOREIGN KEY (B_id)
      REFERENCES B(id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Now I want to make sure that there is at least one instance of B for each instance of A (in other words: each id in A occurs at least once as A_id in C). Is it possible to check this property using database constraints?

Upvotes: 3

Views: 685

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

The better approach for your requirement is a constraint trigger.

It should do an exists() check on table C and raise an exception when the required key doesn't exist. (Which, technically, is what the built-in triggers that enforce foreign key constraints basically do.)

It'll go something like:

create function A_C_fkey_check() returns trigger as $$
begin
  if not exists (select 1 from C where A_id = new.id) then
    raise exception 'invalid key';
  end if;
  return null;
end;
$$ language plpgsql;

create constraint trigger A_C_fkey_check after insert on A
for each row execute procedure A_C_fkey_check();

That said, I'd suggest that actually enforcing this type of constraint at the database-level isn't a great idea: bizarre edge cases that requires an A without a B invariably show up in my own experience.

Upvotes: 1

Related Questions