Postgres Unique Combination Constraint Across Tables

I have three tables - file ( file_id int primary key filename text not null etc... ) product ( product_id int primary key etc.... ) product_attachment ( product_id refe

Solution 1:

If the filename column is not unique you can add a custom constraint on your product_attachment table. Note that this will execute the query below on every insert and update, which is not ideal performance wise.

CREATEOR REPLACE FUNCTION check_filename(product_id integer, file_id integer)
RETURNSbooleanAS
$$
    LOCK product_attachment IN SHARE MODE;
    SELECT (COUNT(*) =0)
    FROM product_attachment pa
    JOIN file f1 ON f1.file_id = pa.file_id
    JOIN file f2 ON f1.filename = f2.filename
    WHERE pa.product_id = $1AND f2.file_id = $2
$$
LANGUAGE'plpgsql'ALTERTABLE product_attachment
ADDCONSTRAINT check_filename CHECK
(check_filename(product_id, file_id))

Solution 2:

Why not just add a unique constraint to product_attachment?

create unique index idx_product_attachment_2 onproduct_attachment(product_id, file_id);

This assumes that the file name is unique, which you can ensure by defining the file name to be unique in that table.