PostgreSQL Unique Constraint with Exclusions
check-constraints, indexing, postgresql, sql, unique-constraint
Solution
Use a partial unique index:
CREATE UNIQUE INDEX transint_partial_uni_idx
ON dbo."TransportInteraction" ("TaskId", "TransportActionId")
WHERE TransportActionId NOT IN (2,3,4);
Related:
- Create unique constraint with null columns
- Unique combination in a table
Problem
I have a table: ``` CREATE TABLE dbo."TransportInteraction" ( "Id" bigint NOT NULL DEFAULT nextval('dbo."TransportInteraction_Id_seq"'::regclass), "Message" character varying(256), "TransportActionId" integer NOT NULL, "TimeCreated" timestamp without time zone NOT NULL, "TimeInserted" timestamp without time zone NOT NULL, "TaskId" bigint ) ``` Generally, this table is mapping actions on a task. `TransportActionId` is an `integer` defining the type of action. Some action types must be unique per task and some others not. So I need a constraint of type: `UNIQUE ("TaskId", "TransportActionId")` applying to all actions with `TransportActionId != (2||3 || 4)`. So all actions except those with `ActionId=2,3 or 4`. Any ideas?