Menu
I have a line_items table with following columns:
product_id
variant_id
variant_id is nullable.
Here is the condition:
variant_id is NULL then product_id should be unique. variant_id has a value then combination of product_id and variant_id should be unique.Is that possible in PostgreSQL?
667
The syntax for creating a unique constraint using an ALTER TABLE statement in PostgreSQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
Right-click the table on which you want to create a unique index and select Design. On the Table Designer menu, select Indexes/Keys. In the Indexes/Keys dialog box, click Add. Select the new index in the Selected Primary/Unique Key or Index text box.
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate. Partial indexes are great when you need to filter a specific subset of data.
Create a UNIQUE multicolumn index on (product_id, variant_id):
CREATE UNIQUE INDEX line_items_prod_var_idx ON line_items (product_id, variant_id);
However, this would allow multiple entries of (1, NULL) for (product_id, variant_id) because NULL values are not considered identical.
To make up for that, additionally create a partial UNIQUE index on product_id:
CREATE UNIQUE INDEX line_items_prod_var_null_idx ON line_items (product_id)
WHERE variant_id IS NULL;
This way you can enter (1,2), (1,3) and (1, NULL), but neither of them a second time. Also speeds up queries with conditions on one or both column.
Recent, related answer on dba.SE, almost directly applicable to your case:
168
Another option is to use expressions in your key fields. This may not have been around when you asked the question, but could be helpful for others that come across this now.
CREATE UNIQUE INDEX line_items_prod_id_var_id_idx
ON line_items ( product_id, (coalesce(variant_id, 0)) );
Granted, this assumes that your variant_id is an auto-incrementing integer that started at 1. Also note the parentheses around the expression. Per the docs, they are required.
http://www.postgresql.org/docs/9.3/static/sql-createindex.html
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
