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.


2 Answers 2


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:

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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