Almost unique constraint in MySQL

Question

Is is possible to have a key in MySQL 5.7, which is unique for all values except one. Let's say -1 is a not assigned wildcard. Is it possible to have a table with the following structure

user | car | seat
1 | 1 | -1
2 | 1 | -1
3 | 1 | 2

That will allow

INSERT INTO table VALUES (4, 1, 3)
INSERT INTO table VALUES (5, 1, -1)

and fail

INSERT INTO table VALUES (4, 1, 2)

Same applies for updates.

• Akina: Let's say -1 is a not assigned wildcard. -1 - no. Use NULL.
• Jon of All Trades: Related: dba.stackexchange.com/questions/43/…

Answer 1

You can use a generated column as:

create table t
( user int not null primary key
, seat int not null
, gen int generated always as ( case when seat = -1 then -1*user 
 else seat 
 end ) stored
, constraint xx unique (gen)
);
-- valid
insert into t (user, seat) values (1,-1), (2,-1), (3,2), (4,3), (5,-1);
-- invalid, Error: ER_DUP_ENTRY: Duplicate entry '2' for key 'xx'
insert into t (user, seat) values (6, 2);

Note that this assumes that users are not negative numbers.

EDIT: You may also consider normalizing your relation in two:

CREATE TABLE users
( user int not null primary key
, ...
);
CREATE TABLE user_seats
( user int not null references users (user)
, seat int not null primary key
-- if a user can only occupy one seat
, constraint ... unique (user)
);

Answer 2

Yes, you can (ab)use NULL for this. Unique indexes allow multiple rows where the unique field is NULL. You just have to make sure you make the field nullable.

Note that this only applies to unique keys, not primary keys - primary keys cannot be nullable.