0

Is it possible to create a unique constraint over three columns in mySQL, in conjunction with a specific value in one of the columns?

Example: This is a table with links between a user (id 1000) and its properties

user_id, properties_id, value,        active, date_add
1000,    1004,          "011 23456",  1,      2020-08-11
1000,    1005,          "Toyota",     1,      2020-08-11
1000,    1005,          "Honda",      0,      2015-01-01
1000,    1005,          "BMW",        0,      2002-01-01

The properties define the meaning of the column value:

1004 = Telephone number of the user
1005 = Car brand of the user

The user has already had 3 car brands in his life, so there are three entries with the properties ID 1005, one of which is active because this is his current car. The others are inactive.

I want to create a constraint that allows several combinations of user_id, properties_id and active, but only if active = 0. There must not be several identical properties that are all active. (Even if a user can have several cars, he should always have only one current car in this data model).

Can mySQL map such a thing?

Greeting Markus

Shorty
  • 43
  • 6
  • I don't think this can be specified as a constraint. You can check for it in a trigger. – Barmar Aug 11 '20 at 19:32
  • However, it seems like this should be in your application logic, not a database constraint. – Barmar Aug 11 '20 at 19:32
  • @Barmar: Thanks for your assessment. Then I won't spend more time searching for a MYSQL solution. – Shorty Aug 11 '20 at 19:35
  • check https://stackoverflow.com/questions/866061/conditional-unique-constraint – limido Aug 11 '20 at 19:36
  • @limido That's SQL-Server, it might not work in MySQL. But it can't hurt to try. – Barmar Aug 11 '20 at 19:39
  • @limido, according to [this](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html): "Stored functions and user-defined functions are not permitted". – Hernán Alarcón Aug 11 '20 at 19:41

0 Answers0