2

Suppose I have four columns

| x | y | z | w |

I would like the following constraint:

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

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

Is this possible?

4

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

Maybe something similar to this would be of help (use trigger(s) as @McNets has suggested): create 2 triggers, one for each "unique" check required (article about emulating MySQL check constraints to be found here). MySQL version used: 5.7

drop trigger xyzunique;

delimiter $
create trigger xyzunique before insert on uc
for each row
begin
  if new.w is null then
    if exists (
      select *
      from uc
      where x = new.x and y = new.y and z = new.z 
    )
    then
      signal sqlstate '45000' set message_text = 'xyZ unique trigger: bad number';
    end if;
  end if;
end$
delimiter ;

drop trigger xywunique;

delimiter $
create trigger xywunique before insert on uc
for each row
begin
  if new.w is not null then
    if exists (
      select *
      from uc
      where x = new.x and y = new.y and w = new.w 
    )
    then
      signal sqlstate '45000' set message_text = 'xyW unique trigger: bad number';
    end if;
  end if;
end$
delimiter ;

Table (for testing)

create table uc ( x integer, y integer , z integer , w integer ) ;

Insert tests: every second/subsequent insert must fail, when using identical values.

 -- insert test xyz unique: second insert must fail
mysql> insert into uc (x,y,z,w) values (1,11,111,null) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into uc (x,y,z,w) values (1,11,111,null) ;
ERROR 1644 (45000): xyZ unique trigger: bad number

-- insert test xzw unique: second insert must fail
mysql> insert into uc values (9,99,999,9999) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into uc values (9,99,999,9999) ;
ERROR 1644 (45000): xyW unique trigger: bad number

Check:

mysql> select * from uc;
+------+------+------+------+
| x    | y    | z    | w    |
+------+------+------+------+
|    1 |   11 |  111 | NULL |
|    9 |   99 |  999 | 9999 |
+------+------+------+------+
2 rows in set (0.00 sec)

This will need more testing, of course. Also, the 2 triggers could be merged into one etc.

  • 1
    Don't forget you probably want to account for updates as well as inserts. – LowlyDBA Jun 16 '17 at 18:02
  • @John M: very good point! – stefan Jun 16 '17 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.