1

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?

2 Answers 2

0

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.

0

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:

About IDENTITY:

6
  • But how would you associate documentation with multiple product? Commented Apr 16, 2023 at 13:04
  • This assumes the same documentation is only applicable to a single product/type combination. Else my model is not a good fit. Commented Apr 16, 2023 at 13:15
  • Thanks, but what if I like to be able to change type of an exisitng document? That's a reason I'm look for a constaint and not to make it part of PK.
    – abobov
    Commented Apr 16, 2023 at 15:36
  • @abobov Perhaps you can clarify: you want to be able to change the type of an existing document? What happens if an associated product already has a document of that new type? Commented Apr 16, 2023 at 16:06
  • @Charlieface If an associated product already has a document of that new type - update should fail.
    – abobov
    Commented Apr 16, 2023 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.