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.