Multi-field unique constraint where we include one or another field based on nullity Suppose I have four columns

| x | y | z | w |


I would like the following constraint:


If w is NULL, then x, y, z must be unique.
If w is not NULL, then x, y, w must be unique.


Is this possible?
First, for constraint number 2, nothing special is needed. A UNIQUE constraint ignores - and accepts - NULL values so a simple unique constraint on (w,x,y) will work just fine enforcing that.

For constraint number 1, I don't think there's a way with DDL in version 5.6 so your best option is probably the trigger solution by @stefan.

If you can upgrade to 5.7, you can use a GENERATED column and a unique index on it:

ALTER TABLE t
  ADD wz int GENERATED ALWAYS AS (CASE WHEN w IS NULL THEN z END),
  ADD UNIQUE INDEX x_y_z_uq (x, y, wz) ;