Chris Chris - 4 months ago 19
MySQL Question

MySQL UNIQUE CONSTRAINT failing in CREATE TABLE with subsequent INSERT

Using phpMyAdmin and MySQL v5.5.49 consider:

CREATE TABLE op_sys (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
version VARCHAR(255) NOT NULL,
-- UNIQUE KEY name_version (name, version)
-- CONSTRAINT name_version UNIQUE (name, version)
-- UNIQUE(name, version)
-- CONSTRAINT UNIQUE(name, version)
)ENGINE=InnoDB;


I've tried all four of the commented out attempts to simply stop INSERT INTO sys_op duplicate values for "name" and "version". All four are processed without error.

The insert into:

INSERT INTO op_sys(name, version)
VALUES ('ANDROID','ANDROID');


executes "successfully". ANDROID ANDROID is now a row. Where have I gone wrong or what step am I not aware of? I've checked the MySQL manual and several different posts here that seem to say I'm doing it correctly... Thanks.

Answer

You seem to misunderstand what UNIQUE KEY means:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

If your table has UNIQUE(name, version), then you can do:

INSERT INTO op_sys(name, version) VALUES ('ANDROID','ANDROID');

But, the next time you do it, it will fail because the table already holds a record with the same pair(name, version) as in the record you want to insert.

To prevent inserting a record that has the same value for name and version`, you could use a trigger:

CREATE TRIGGER different_values BEFORE INSERT ON op_sys
FOR EACH ROW BEGIN
 DECLARE identical_values CONDITION FOR SQLSTATE '45000';
 IF NEW.name = NEW.version THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Identical values for name and version';
 END IF;
END;

It will run before each INSERT on table sys_op, and if the name and version fields hold identical values, it will generate an error and the insertion will fail.

Documentation for SIGNAL