MySQL UNIQUE CONSTRAINT failing in CREATE TABLE with subsequent INSERT

Question

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.

Jocelyn · Accepted Answer · 2016-07-15 16:23:30Z

2

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.

The error returned looks like this:

ERROR 1644 (45000): Identical values for name and version

Documentation:
- CREATE INDEX
- CREATE TRIGGER
- SIGNAL

edited Jul 15, 2016 at 16:23

answered Jul 15, 2016 at 16:00

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RigidBody Over a year ago

Thanks, that's exactly what I was looking for. Cheers!

Shadow · Accepted Answer · 2016-07-15 16:02:10Z

2

A multi-column unique index prevents you to have the same 2 values for these 2 fields between records. This means, you cannot have 2 records, where name and version are 'ANDROID','ANDROID'. However, a unique index does not prevent these fields from having the same value within a single row.

You either have to implement this control in application level, where you check if the 2 field values are the same and if yes, then do not do the insertion.

In the database layer you could ad a before insert trigger and check the 2 fields' value there and raise a custom error message using the signal command.

But I have such a de ja vu feeling. As if you had asked this question before and you could not do an if() in php...

answered Jul 15, 2016 at 16:02

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

1 Comment

RigidBody Over a year ago

No, first time. Yes, easy on the php side - just check if the value is in the column prior to executing the insert. I'll explore triggers, as this seems to be what I'm missing. Thanks!

Collectives™ on Stack Overflow

MySQL UNIQUE CONSTRAINT failing in CREATE TABLE with subsequent INSERT

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related