CakePHP 3 Multiple isUnique, allowing duplication of NULL

Question

I have the following rule in my BlockedTable.php

public function buildRules(RulesChecker $rules) { $rules->add($rules->isUnique(['date', 'time']), ['message' => 'unique error']); return $rules; } 

So far this has worked fine - if I try to save a new record with an existing date and time, this will not allow me to save.

However, if my time is NULL , for example, the next entry;

 ╔════╦══════════════╦═══════╗ ║ ID ║ Date ║ Time ║ ╠════╬══════════════╬═══════╣ ║ 1 ║ 22/08/1985 ║ NULL ║ ╚════╩══════════════╩═══════╝ 

This rule allows me to save a new record with the same data. Therefore, if I try to save $date = 22/08/1985 and $time = NULL , it saves a fine, and there is a duplicate in my database. I expected him to fail due to the above rule?

Why is this happening? And how can I prevent duplicate entries in NULL values?

Thanks in advance for your help.

Answer 1

Comparison with NULL using normal comparison operators, i.e. column = NULL (which is the only rule) should always be NULL (AFAIK this, at least, occurs in MySQL and Postgres), so nothing is found, and therefore a unique check will pass whenever there is a NULL value.

If you want to prevent this behavior, you will have to use a custom rule, since the built-in rule simply does not support it. I think this is worth the improvement, so you can open a ticket on GitHub .

Here is a basic example for the IsUnique override rule class, it basically just adds the IS statement to the condition key, so NULL checks completion as column IS NULL .

 public function __invoke(EntityInterface $entity, array $options) { if (!$entity->extract($this->_fields, true)) { return true; } $alias = $options['repository']->alias(); $conditions = $this->_alias($alias, $entity->extract($this->_fields)); if ($entity->isNew() === false) { $keys = (array)$options['repository']->primaryKey(); $keys = $this->_alias($alias, $entity->extract($keys)); if (array_filter($keys, 'strlen')) { $conditions['NOT'] = $keys; } } // handle null values foreach ($conditions as $key => $value) { if ($value === null) { $conditions[$key . ' IS'] = $value; unset($conditions[$key]); } } return !$options['repository']->exists($conditions); } 

Theoretically, you could also do this with the overriden IsUnique::_alias() method, which would work without having to redefine the code from the source rule class, but that really is not the case.

https://github.com/cakephp/cakephp/blob/3.2.5/src/ORM/Rule/IsUnique.php

see also

• Cookbook> Validation> Applying Application Rules> Creating Custom Rule Objects