MySQL - Prevent duplicate records in table via index?

Question

Using MySQL 5

I have a table like this:

date (varchar)
door (varchar)
shift (varchar)
route (varchar)
trailer (varchar)
+ other fields

This table contains user generated content (copied in from another 'master' table) and to prevent the users from creating the same data more than 1x the table has a unique index created based on the fields specified above.

The problem is that the "duplicate prevention" index doesn't work.
Users can still add in duplicate records with no errors being reported.

Is this problem due to my not understanding something about how indexes work?

Or

Is it a possible conflict with the primary key field (autoincrementing int)?

The CREATE TABLE looks like this:

CREATE TABLE /*!32312 IF NOT EXISTS*/ "tableA" (
"Date" varchar(12) default NULL,
"door" varchar(12) default NULL,
"Shift" varchar(45) default NULL,
"route" varchar(20) default NULL,
"trailer" varchar(45) default NULL,
"fieldA" varchar(45) default NULL,
"fieldB" varchar(45) default NULL,
"fieldC" varchar(45) default NULL,
"id" int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY  ("id"),
UNIQUE KEY "duplicate_preventer" ("Date","door","Shift","route","trailer"),

A row duplicated is:

date       door  shift      route    trailer

10/4/2009  W17   1st Shift  TEST-01  NULL
10/4/2009  W17   1st Shift  TEST-01  NULL

Answer 1

Are you sure that you are using unique index instead of a normal index?

create unique index uix on my_table (date, door, shift, route, trailer);

Also that kind of index only makes sure that combination of fields is unique, you can for example have several duplicate dates if, for example, field door is different on every row. The difference could something that is hard to spot, for example a space in end of the value or lowercase/uppercase difference.

Update: your unique index seems to be in order. The problem is elsewhere.

Answer 2

Users can still add in duplicate records with no errors being reported.

What do you mean by "duplicate records"?

Depending on collation, case, accent etc. may matter, and 'test' and 'TEST' will not be considered duplicates.

Could you please post the results of SHOW CREATE TABLE mytable?

Also, could you please run this query:

SELECT  date, door, shift, route, trailer
FROM    mytable
GROUP BY
        date, door, shift, route, trailer
HAVING  COUNT(*) > 1

If it returns the rows, the problem is with the index; if it does not, the problem is with your definition of a "duplicate".

Update:

Your columns allow NULLs.

NULL values in MySQL are not considered duplicate from the point of view of a UNIQUE index:

CREATE TABLE testtable (door VARCHAR(20), shift VARCHAR(15), UNIQUE KEY (door, shift));

INSERT
INTO    testtable
VALUES
('door', NULL),
('door', NULL);

SELECT  door, shift
FROM    testtable
GROUP BY
        door, shift
HAVING  COUNT(*) > 1;

From documentation:

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. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Answer 3

I think you'd want to create a unique constraint on the fields you don't want duplicated. This will in turn create a unique index.

Like this:

ALTER TABLE YourTable ADD CONSTRAINT uc_yourconstraintname UNIQUE (date, door, shift, route, trailer)