Hello.

There is a table with a unique composite index of 2 fields, here’s a query to create it:

CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `field1` int(11) unsigned DEFAULT NULL, `field2` int(11) unsigned NOT NULL, `field3` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq` (`field1`,`field2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I don’t understand why I can add the same records, provided that the field1 field is NULL. Here is an example of filling the table:

 INSERT INTO `test` (`id`, `field1`, `field2`, `field3`) VALUES (1, 1, 1, 1), (3, 1, 2, 1), (4, NULL, 1, 1), (5, NULL, 1, 1), (6, NULL, 1, 1);

    1 answer 1

    Taken from here :

    For all engines, it can be NULL.

    That is, in essence, by allowing NULL values ​​to be inserted into one of the columns, you yourself broke the UNIQUE index.

    • Formally speaking, the uniqueness is preserved at the same time, since a comparison of two NULLs is false. - Yaant
    • Hmm, really. Strange mysql. How do I understand this can not be avoided and just accept? - Ivan Torgov
    • @IvanTorgov everything depends on what you need. In your example, this is not clear. Either make the field not null, or, if the fields are more correct, and not field1 / field2 / field3, then maybe you need another table with its link and this table via the foreign key - Alexey Shimansky
    • @Yaant SELECT NULL != NULL gives null ...... SELECT NULL = NULL gives null ...... so what kind of integrity are you talking about - Alex Shimansky
    • You can avoid this in one way. Make all fields index NOT NULL. Since you fill them up with values ​​explicitly, according to an example, select some formally impossible value (the data shown will come down, for example, MAXUINT = 4294967295), and use it instead of NULL. Unfortunately, all queries that use assigning a null value to a field explicitly will have to be rewritten ... - Akina