CODEWITHSUNDEEP
mysqlunique-constraintunique-key
agb
agb

Reputation: 122

Mysql UNIQUE Constraint that only applies if one field has specfic values

I hope this works somehow:

I have a Mysql Table with 3 column:

id = int
state = enum('visible', 'moderated', 'deleted')
user_id = int

No user should have more than 1 entry, that is 'visible' or 'moderated', but he can have endless 'deleted' entries.

So, I need a UNIQUE Key on user_id, that only applies if the state is 'visible' or 'moderated'

Upvotes: 0

Views: 637

Answers (1)

rogers
rogers

Reputation: 71

There are basically two options, first one is fairly simple but would require change in your table structure and application logic.

If you use NULL value instead of 'deleted', you can have as many "deleted" rows as you want for given user_id (having unique constaint on (user_id, state)). Your table structure would be something like this:

id = int
state = enum('visible', 'moderated') NULL
user_id = int

The other option would involve checking in post update/post insert triggers whether you are not breaking your contraint and throwing "sql exception" vide https://dev.mysql.com/doc/refman/5.5/en/signal.html

Upvotes: 1

Related Questions