0

I'm stumbled on a tricky problem today, I want to restrict the number of appearance of some values of a field, here's my table:

CREATE TABLE `test_table` (
  `id` varchar(40) COLLATE utf8_bin NOT NULL,
  `create_time` datetime NOT NULL DEFAULT '2010-01-01 00:00:00',
  `user_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `device_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `is_owner` boolean NOT NULL,
  `user_nickname` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_device` (`device_id`,`is_owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

For example, we have two users Bob and Tom, and one device Echo, the relationships among user_id, device_id and is_owner are:

  1. A device ONLY can have one owner, which means only a pair of (device: Echo, is_owner: true) is allowed.
  2. A device can have multiple users, this is to say that multiple appearance of the pair(device: Echo, is_owner: false) is allowed.

The unique constraint won't help in my case, please help.

Jerry Chin
  • 657
  • 1
  • 8
  • 25
  • You have the wrong data format. You should have a table with one row per device, and that should have the owner. You should have a separate table with one row per device and user. – Gordon Linoff May 10 '17 at 12:03
  • You can enforce this in the application logic, or in the query itself – Strawberry May 10 '17 at 12:06
  • @Gordon Linoff It requires a ton of work to do, the current architecture of our application is not stable, we've been trying to dodge any major changes which will introduce more *bugs*. – Jerry Chin May 10 '17 at 23:36
  • @Strawberry Yeah, I agree with you, but if it can be done by the DBMS, I'll feel more assured. – Jerry Chin May 10 '17 at 23:41

3 Answers3

1

The solution might be to use mysql triggers instead of constraints. Cou can bind triggers to certain events and prevent or modify the action that takes place. In general constraints won't help you if you want to model data with complex multiplicity in it. Here is an example (not tested) how to create a MYSQL trigger for your problem:

CREATE TRIGGER my_trigger BEFORE INSERT ON test_table 
    FOR EACH ROW
BEGIN
  IF NEW.is_owner = TRUE AND EXISTS (SELECT * FROM test_table WHERE device_id = NEW.device_id AND is_owner = TRUE) THEN
    SIGNAL sqlstate '99999'
        SET message_text = "Can't insert because an other owner exists for that device";
  END IF;
END$$

In this example an SQL Error will be created when someone tries to create a second entry with ownership and with the same device id. You can take a look at the manual for further information.

michip96
  • 363
  • 3
  • 12
  • In a distributed scenario, what if two transactions try to write the same data simultaneously, the trigger will likely fail to guarantee the restrictions. – Jerry Chin May 10 '17 at 23:24
  • And I want to apologize, I should have mentioned that there's a similar trigger much like yours, but when my colleague tried to insert dozens of data after applied it to the DBMS, for some unknown reason its effectiveness is NOT observed, it complains randomly even if the data to be inserted is NOT existed in the DBMS, and more importantly we can't fully *test* this script, so bugs are hardly avoided. – Jerry Chin May 10 '17 at 23:52
  • Are you using MySQL transactions properly? You might fail on correctly inserting data in the database. If you are inserting data in multiple tables (which I assume in your example) you should pack all insertions into one transaction and commit them all at once. You can take a look at https://dev.mysql.com/doc/refman/5.7/en/commit.html for further information on transactions. – michip96 May 14 '17 at 12:43
  • @michp96 I'm using console to submit some testing samples, I'll stop struggling, Shadow's answer works anyway. – Jerry Chin May 15 '17 at 01:44
  • @JerryChin MySQL has a feature called autocommit which is most likely the feature you are using while inserting the data. It means that after every insertion your changes will be directly saved into the database. If you have a complex table structure where certain rows depend on each other you can pack multiple insertion statements into one transaction. In that case your insertions in multiple tables will be saved at once and not after one another. If you are using triggers they can only work properly if you insert the data correctly. Even if you already fixed your bug this might help others. – michip96 May 15 '17 at 08:43
1

If you use null instead of false to indicate that a user is not an owner, then you can use a unique index to provide this restriction because in MySQL unique indexes allow multiple null values. Obviously, you need to make the is_owner field nullable for this to work.

Otherwise, this control is better placed in the application layer, than in the database. In the database you can use a trigger to check this condition and prevent extra owner records to be set by raising an sql error.

Community
  • 1
  • 1
Shadow
  • 33,525
  • 10
  • 51
  • 64
-1

You should use a constraint on device_id and user_id for the unique relationship between users and devices (owners or not). With this scheme, you cant have a unique constraint for "only one owner per device" (or at least I cant see one).

Alex
  • 566
  • 3
  • 15