|
NULLs in the index allow for duplicate keys. NULLs are required due to foreign keys.
Demo Script to show that values with zero are unique but records with NULL are in the table twice:-
DROP TABLE IF EXISTS `test_table`;
|
CREATE TABLE `test_table` (
|
`id1` INT(10) NOT NULL,
|
`id2` INT(10) NULL DEFAULT NULL,
|
`id3` INT(10) NULL DEFAULT NULL,
|
`id4` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`data` TEXT,
|
UNIQUE INDEX `main` (`id1`, `id2`, `id3`, `id4`)
|
)
|
COLLATE='latin1_swedish_ci'
|
ENGINE=InnoDB
|
;
|
INSERT INTO `test_table` (`id1`,`id2`,`id3`,`id4`,`data`)
|
VALUES (1,NULL,NULL,'2020-02-20 10:00:00','data1'),
|
(1,NULL,NULL,'2020-02-20 10:00:00','data2'),
|
(2,0,0,'2020-02-20 10:00:00','data1'),
|
(2,0,0,'2020-02-20 10:00:00','data2')
|
ON DUPLICATE KEY UPDATE `data` = VALUE(`data`);
|
|