CODEWITHSUNDEEP
mysqlsql
Jerry Chin
Jerry Chin

Reputation: 667

MySQL restricting the value of a MySQL field (allow one specific value and multiple others.)?

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.

Upvotes: 0

Views: 1919

Answers (3)

Shadow
Shadow

Reputation: 34232

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.

Upvotes: 1

michip96
michip96

Reputation: 363

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.

Upvotes: 1

Alex
Alex

Reputation: 566

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

Upvotes: -1

Related Questions