SQL table with constraint on multiple NULL

user_10522758 0
Viewed 58

I am using MYSQL and I have a table of devices. Each device has, among the others, an ID, called mac_address (this is not the pk), and a column named deleted_date.

If a device is not active, its deleted_date equals to a timestamp, and then it should be ok to add a new device with the same mac_address. If it's active, its deleted_date equals to NULL and then it should not be ok to add a new device with the same mac_address.

I found out that it was possible to have two devices that are still active with the same mac_address, which is wrong. After some research, I figured out that I can't compare NULL to NULL since it does not count as the same value.

I would appreciate an idea for how to get around this.

  • Two devices with the same mac_address, and deleted_date is NULL: WRONG
  • Two devices with the same mac_address, one deleted_date is NULL and the other has a value: OK
  • Two devices with the same mac_address, and deleted_date has a value for both of them: OK

Right now this is the relevant part of the table creation and constraints:

'CREATE TABLE `iot_devices` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) DEFAULT NULL,
  `device_name` varchar(100) NOT NULL,
  `delete_date` timestamp NULL DEFAULT NULL,
  `mac_address` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `idx_device_id` (`device_id`),
  UNIQUE KEY `idx_mac_address_delete_date` (`mac_address`,`delete_date`),
)
constraints mysql
3 Answers

You cannot do this on an INSERT statement alone but you can with a trigger for example.

drop trigger if exists t;
delimiter $$

create trigger t before insert on t
for each row
begin
        declare vcount int default 0;
        select ifnull(count(*),0) into vcount from t where mac_address = new.mac_address and delete_date is null; 
       if vcount <> 0 then
            SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'An error occurred';
        end if;
end $$
delimiter ;

Note you will probably have to do something similar on an UPDATE.

user_61524000

We will need to identify and solve the following:

  • what are possible problems that we could already have
  • how to handle an insert
  • how to handle an update

Possible problem

You might have several records with the same mac_address and null delete_date. You will need to make sure that you have a single record per each mac_address that has a null delete_date. If you want to use a constraint for unique, you can set the non-deleted records' delete_date to some date in the very distant future and will know that records having a delete_date in the future are not deleted. If that future date is a constant, then that would solve your problem, but this solution is not very elegant. I would still use the null date instead but then you need other measures.

Triggers before insert or update

P.Salmon's answer describes this kind of approach, I will not duplicate it, but upvote his answer instead. The problem with triggers is that in the case when there are more records to be inserted/updated and the trigger throws an error early on, then some valid insertions or modifications will not happen.

Problems with insert and update

The problem with insert is the possibility that the new record would match a mac_address with null delete_date. The problem with update is that the updated record would result with a duplicated mac_address with another record which has a null delete_date. You can do an insert select or update with a condition like:

where not ((not (iot_devices.deleted_date is null)) and (iot_devices.mac_address = 'youvalue'))

Handling older records as we go

You can update delete_date of older records as a trigger on your insert or update

'' instead of null

You can change the type of your delete_date to varchar and have '' to non-deleted values. Then you can use a constraint. However, searching by delete_date will become more difficult in this case.

user_4365600

You are right NULL cannot be compared with NULL

but you can compare '' with '' so

Use '' empty string not NULL as default value

user_116079860
Related