MySQL NULLs I say almost any database because MySQL has an odd quirk. NULL is treated as a unique value — which is why you cannot use comparisons such as value = NULL and need to use value IS NULL. Unfortunately, this also affects unique indexes and no logic has been implemented to fix it.
We can execute our original INSERT multiple times and a new record will be created each time because the extension field defaults to NULL and is considered to be unique:
INSERT INTO phone
(country, area, number)
(1, 234, 567890);
Yes, it’s insane. I’m not aware of the problem in other databases and even MySQL works as expected if you’re using the BDB storage engine. It’s been reported as a MySQL bug but there are no known plans to fix it.
The solution: ensure all fields defined in a unique index cannot be set to NULL. In this example, we could indicate that there’s no extension number by setting a value such as 0 or 99999. Or perhaps we could make the field a signed number and set -1. It’s horrible, but it’ll work.