Design many-to-many join table with additional column

Question

I have product and documentation tables and want to hava a many-to-many relationship between them with an additional condition.

The tables look like this:

create table product (
  id bigserial primary key
);

create table documentation (
  id bigserial primary key,
  type text not null
);

And condition is: each product can have any number of documentation but no more than 1 of each type.

Is it possible to create such a constraint without addading a type column into the join table?

Answer 1

No, the only way to do this is to bring in the type column into the join table.

First add a unique key across id, type

alter table documentation
  add unique (id, type);

Then create a join table. This will have a foreign key to that unique key. It will also have a primary key across the three columns, and a unique key across product_id, type.

create table product_documentation (
  product_id bigint not null references product (id),
  documentation_id bigint not null,
  type text not null,
  foreign key (documentation_id, type) references documentation (id, type),
  primary key (product_id, documentation_id, type),
  unique (product_id, type)
);

db<>fiddle

---

In SQL Server, which supports indexed views, you can use an indexed view to enforce the constraing instead. Note that this is not the same as a materialized view in Postgres and Oracle.

create table product_documentation (
  product_id bigint not null references product (id),
  documentation_id bigint not null references documentation (id),
  primary key (product_id, documentation_id)
);

create view dbo.product_documentationType
with schemabinding
as

select
  pd.product_id,
  d.type
from dbo.product_documentation pd
join dbo.documentation d on d.id = pd.documentation_id;

create unique clustered index ucx on dbo.product_documentationType
  (product_id, type);

However the benefit of doing so as opposed to the above design is questionable.

Answer 2

I have product and documentation tables and want to have a many-to-many relationship between them ...

Actually, what you describe is a many-to-many relationship between product and type, documentation being an attribute of the link table (which may also be named "documentation").

A minimal implementation in modern Postgres:

CREATE TABLE product (
  prod_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-- more 
);

CREATE TABLE prod_type (
  prod_type text PRIMARY KEY
-- more ?
);

CREATE TABLE documentation (
  prod_id bigint REFERENCES product
, prod_type text REFERENCES prod_type
, documentation text  -- NOT NULL ?
, PRIMARY KEY (prod_id, prod_type)
);

This way, a product can have one documentation per existing type.

If product types are lengthy strings, consider a surrogate PK for the prod_type table and use that as FK reference.

If you don't enforce NOT NULL for documentation.documentation, then you allow to link a product to a type without providing actual documentation. This may or may not be desirable. (Think of transient states.) All other columns in my schema are NOT NULL implicitly.

If the same documentation is applicable to multiple product/type combinations, break out documentation into its own table. The link table then implements a ternary relationship with an added doc_id (and a different table name, obviously), but still only (prod_id, prod_type) is unique - can be the PK.

Use descriptive names. "type" and "id" are too ambiguous for my taste - "id" being a wide-spread anti-pattern.

Basics:

• How to implement a many-to-many relationship in PostgreSQL?

About IDENTITY:

• Auto increment table column