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) ;

StackExchange Database Administrators Q#176411, answer score: 6