MySQL - prevent duplicate entries in a table through an 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 from another "master" table), and so that users cannot create the same data more than 1 time, the table has a unique index created based on the above fields.

The problem is that the “duplication of prevention” index does not work.
Users can add double entries without error messages.

Is this problem due to the fact that I do not understand how indexes work?

or

Is there a conflict with the primary key field (int auto increment)?

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"),

The line is repeated:

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

.

" "?

, .., 'test' 'test' .

SHOW CREATE TABLE mytable?

, :

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

, ; , "".

Update:

NULL s.

NULL MySQL UNIQUE:

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;

:

A UNIQUE index , . , , . NULL, BDB. UNIQUE NULL , NULL. UNIQUE, .

Answer 2

, ?

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

, , , , , , . , , / .

: , , . .

Answer 3

I think you need to create a unique restriction on fields that you do not want to duplicate. This, in turn, will create a unique index.

Like this:

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