MySQL不尊重UNIQUE和PRIMARY键

Question

Hey.
To start with, I have to say that this is the first time I have ever tried to write SQL, which means I'm a n00b. Have some patience, please..

Now, I'm trying to create a table called "push" in my database like this:

CREATE TABLE push
(id int NOT NULL AUTO_INCREMENT,
UDID varchar(40) NOT NULL,
token varchar(64) NOT NULL,
lastpost int DEFAULT '0',
PRIMARY KEY(id),
UNIQUE KEY(id, UDID, token));

That works, but not as expected. If I now try to insert some values here like this:

INSERT INTO push (UDID, token, lastpost)
VALUES ('123456789abcdefghijklmnopqrstuvwxyz', 'abcdefghijklmnopqrstuvwqyz123456789', 211);
INSERT INTO push (UDID, token, lastpost)
VALUES ('123456789abcdefghijklmnopqrstuvwxyz', 'abcdefghijklmnopqrstuvwqyz123456789', 211);

That would, in my eyes, cause an error, because the UDID and token are equal, but it does not trigger any error at all, it just inserts the duplicate.

I might have missed something here, but I can't find out what. How can I make this return the expected result?
Thanks.

Answer 1

this:

UNIQUE KEY(id, UDID, token));

Means that the combination of those 3 should be unique. The id field (auto-increment) will be different for the 2 rows, so it will satisfy that rule

If the combination should be unique, make it without the id

UNIQUE KEY(UDID, token));