同时有两个主键是不可能的。但是(假设你没有把复合键搞砸)，你可能需要的是让一个属性是唯一的。

CREATE t1(
c1 int NOT NULL,
c2 int NOT NULL UNIQUE,
...,
PRIMARY KEY (c1)
);

However note that in relational database a 'super key' is a subset of attributes which uniquely identify a tuple or row in a table. A 'key' is a 'super key' that has an additional property that removing any attribute from the key, makes that key no more a 'super key'(or simply a 'key' is a minimal super key). If there are more keys, all of them are candidate keys. We select one of the candidate keys as a primary key. That's why talking about multiple primary keys for a one relation or table is being a conflict.

主键是唯一标识一条记录并在所有索引中使用的键。这就是为什么你只能有一个。它通常也是用于连接子表的键，但这不是必需的。PK的真正目的是确保某些东西允许您惟一地标识一条记录，这样数据更改就会影响正确的记录，从而可以创建索引。

However, you can put multiple fields in one primary key (a composite PK). This will make your joins slower (espcially if they are larger string type fields) and your indexes larger but it may remove the need to do joins in some of the child tables, so as far as performance and design, take it on a case by case basis. When you do this, each field itself is not unique, but the combination of them is. If one or more of the fields in a composite key should also be unique, then you need a unique index on it. It is likely though that if one field is unique, this is a better candidate for the PK.

Now at times, you have more than one candidate for the PK. In this case you choose one as the PK or use a surrogate key (I personally prefer surrogate keys for this instance). And (this is critical!) you add unique indexes to each of the candidate keys that were not chosen as the PK. If the data needs to be unique, it needs a unique index whether it is the PK or not. This is a data integrity issue. (Note this is also true anytime you use a surrogate key; people get into trouble with surrogate keys because they forget to create unique indexes on the candidate keys.)

There are occasionally times when you want more than one surrogate key (which are usually the PK if you have them). In this case what you want isn't more PK's, it is more fields with autogenerated keys. Most DBs don't allow this, but there are ways of getting around it. First consider if the second field could be calculated based on the first autogenerated key (Field1 * -1 for instance) or perhaps the need for a second autogenerated key really means you should create a related table. Related tables can be in a one-to-one relationship. You would enforce that by adding the PK from the parent table to the child table and then adding the new autogenerated field to the table and then whatever fields are appropriate for this table. Then choose one of the two keys as the PK and put a unique index on the other (the autogenerated field does not have to be a PK). And make sure to add the FK to the field that is in the parent table. In general if you have no additional fields for the child table, you need to examine why you think you need two autogenerated fields.

一个表可以有一个组合主键，它是由两个或多个列组成的主键。例如:

CREATE TABLE userdata (
  userid INT,
  userdataid INT,
  info char(200),
  primary key (userid, userdataid)
);

更新:这里有一个关于复合主键的更详细描述的链接。

一个表可以有多个候选键。每个候选键都是一个列或一组列，它们是UNIQUE的，加在一起也是NOT NULL。因此，为任何候选键的所有列指定值就足以确定是否有一行满足条件，或者根本没有行。

候选键是关系数据模型中的一个基本概念。

通常的做法是，如果一个表中存在多个键，则将其中一个候选键指定为主键。通常的做法是使表的任何外键引用主键，而不是任何其他候选键。

我推荐这些实践，但是关系模型中没有要求在候选键中选择主键。

这是对主要问题和@Kalmi的问题的答案

拥有多个自动生成列的意义何在?

下面的代码有一个复合主键。其中一列是自动递增的。这将只在MyISAM工作。InnoDB将生成一个错误" error 1075(42000):错误的表定义;只能有一个auto列，它必须定义为一个键”。

DROP TABLE IF EXISTS `test`.`animals`;
CREATE TABLE  `test`.`animals` (
  `grp` char(30) NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`grp`,`id`)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

只能有一个主键，但可以在主键中有多个列。

你也可以在你的表上有唯一索引，这将有点像一个主键，因为它们将强制唯一的值，并将加快这些值的查询。