Postgres unique combination constraint across tables

Question

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 references product
  file_id references file
)

I want to ensure that when these are natural-joined, product_id + filename is unique. The best solution I have so far involves adding filename to the product_attachment table, but I'm wondering if there's a way to avoid that.

FuzzyTree · Accepted Answer · 2014-07-23 16:34:22Z

11

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.

CREATE OR REPLACE FUNCTION check_filename(product_id integer, file_id integer)
RETURNS boolean AS
$$
    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 = $1 AND f2.file_id = $2
$$
LANGUAGE 'plpgsql'

ALTER TABLE product_attachment
ADD CONSTRAINT check_filename CHECK
(check_filename(product_id, file_id))

edited Jul 23, 2014 at 16:34

answered Jul 23, 2014 at 12:35

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

qcode peter Over a year ago

I'm not clear why you have a "for update" there, and I think the test should be count(*) = 0 (or using a sub-query with "exists" might yield better performance), but this is exactly the approach I was looking for, thank you!

FuzzyTree Over a year ago

@qcodepeter you're right, it should be count(*) = 0, for update locks the selected rows in order to prevent a race condition

qcode peter Over a year ago

If I understand correctly, the query will either lock and return 0 rows - in which case the lock does nothing - or the constraint will fail - in which case the lock does nothing. Maybe adding a "lock product_attachment in share mode" to the start of the query will do the trick?

FuzzyTree Over a year ago

@qcodepeter if i understood the docs, for update acquires a table level lock but it's probably clearer and safer to explicitly lock the table as you've mentioned. btw if it's possible to update filename you may need a similar constraint on your file table

qcode peter Over a year ago

you're right to say that it acquires a table level lock, but it's only a row share lock, which I don't think will be enough. As it happens, we aren't currently allowing filename updates, so I'm not going to worry about that, but yes, that would otherwise be a consideration.

|

Gordon Linoff · Accepted Answer · 2014-07-23 12:10:49Z

0

Why not just add a unique constraint to product_attachment?

create unique index idx_product_attachment_2 on product_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.

answered Jul 23, 2014 at 12:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

qcode peter Over a year ago

File name is not unique across the entire file table, unfortunately that's a requirement of the system.

Collectives™ on Stack Overflow

Postgres unique combination constraint across tables

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related