MySQL doesn't respect UNIQUE and PRIMARY-keys

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开发者_JS百科 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.

---

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));

---

This bit here

UNIQUE KEY(id, UDID, token));

ensures that you can always enter a duplicate value for UDID, and it will be accepted as the test for uniqeness includes ID; which is defined as auto_increment. Remove ID from this key and you should have the check you want i.e.

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(UDID, token)); -- ID now excluded here

---

If you want both UDID and token to be unique or in other words no two UDID should be allowed to be the same nor two token should be allowed to be the same, try:

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( UDID ),
    UNIQUE KEY( token )
  ) ;