Reputation: 45
I use PostgreSQL and try to add the index below:
CREATE UNIQUE INDEX product_type_serial_pin_unique
ON servicestore.product_unit(serial)
WHERE serial is not null AND product_id = (SELECT id FROM servicestore.product WHERE product_type = 'SERIAL_PIN');
I got the following error:
ERROR: cannot use subquery in index predicate
how can I solve this problem?
Upvotes: 0
Views: 645
Reputation: 247300
The only way you can make that work with a constraint is if you add an attribute to product_unit
that copies product_type
from product
. To make sure they are in sync, add the attribute to a foreign key from product_unit
to product
. Alternatively, you can use triggers to synchronize the redundant data.
Upvotes: 1