mysql 两个字段 主键,两列作为mysql中的主键？

Today I found out you can have a primary key using two columns (tsql). The PK must be unique but both columns do not (the combo must be unique).

I thought that was very cool. There were at least two SO question I asked where people yelled at me that I was doing my (mysql) databases wrong with only one person saying I did it fine. So... this leaves me some doubt

Does this do what I think it does?

create table User(

id INT primary key AUTO_INCREMENT ,

ipaddr TEXT NOT NULL ,

email TEXT NOT NULL

);

create table test(

a INT NOT NULL ,

b INT NOT NULL ,

dummy INT NOT NULL ,

FOREIGN KEY (a) REFERENCES User(id),

FOREIGN KEY (b) REFERENCES User(id),

PRIMARY KEY(a,b)

);

I ran the below so it appears that i is doing what i think (combos must be unique. But same value in a column don't need to be unique). Should i be aware of something? There must be a reason no one mentioned this to me in regards to mysql?

mysql> insert into test(a,b,dummy) select 1,1,1;

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test(a,b,dummy) select 1,2,2;

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test(a,b,dummy) select 2,1,3;

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test(a,b,dummy) select 2,2,4;

Query OK, 1 row affected (0.03 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into test(a,b,dummy) select 1,2,5;

ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'

解决方案

You are over thinking this:

primary keys (no matter how many columns are involved) must be unique

your primary key is the two columns a, b

Therefore a and b together must be unique.

Individual value of a and b are irrelevant.