两列作为mysql中的主键? [英] Two column as a primary keys in mysql?

问题描述

今天我发现您可以使用两列(tsql)来拥有主键. PK必须是唯一的，但两列都必须是唯一的(组合必须是唯一的).

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

我认为那很酷.我至少有两个问题，我问人们在哪里对我大喊:我在(mysql)数据库上做错了，只有一个人说我做得很好.所以...这让我有些疑问

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

我在下面运行，因此看来我在做我认为的事情(组合符必须是唯一的.但列中的相同值不必是唯一的).我应该知道些什么吗?关于mysql一定没有人对我提过这个原因吗?

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'

推荐答案

您正在考虑以下问题:

• 主键(无论涉及多少列)必须是唯一的
• 您的主键是a，b两列

因此，a和b在一起 必须是唯一的.

Therefore a and b together must be unique.

a和b的无关紧要.

这篇关于两列作为mysql中的主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！