这是对主要问题和@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 |
+--------+----+---------+

主键是唯一标识一条记录并在所有索引中使用的键。这就是为什么你只能有一个。它通常也是用于连接子表的键，但这不是必需的。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.

他们给出了比我更好的技术答案。 我只能补充一下这个话题:

如果你想要一些不被允许/可接受的东西，这是一个很好的理由后退一步。

理解为什么这是不可接受的核心原因。 挖掘更多的文档/期刊文章/网络等。 分析/审查当前设计并指出主要缺陷。 在新设计中考虑和测试每一个步骤。 永远向前看，努力创造适应性的解决方案。

希望它能帮助到一些人。

正如其他人所指出的，可以有多列主键。 但是需要注意的是，如果有一些函数依赖关系不是由键引入的，那么应该考虑规范化关系。

例子:

Person(id, name, email, street, zip_code, area)

id ->名称、电子邮件、街道、zip_code和区域之间可能存在功能依赖关系 但zip_code通常与区域相关联，因此zip_code ->区域之间存在内部函数依赖关系。

因此，可以考虑将它分割到另一个表中:

Person(id, name, email, street, zip_code)
Area(zip_code, name)

所以它和第三种形式是一致的。

(我一直在研究这些，很多)

候选键——唯一标识表行所需的最小列组合。 复合键- 2个或更多列。

一个表中可以存在多个候选键。 主键-只有一个候选键是由我们选择的 备用键-所有其他候选键 主键和备用键都可以是复合键

来源: https://en.wikipedia.org/wiki/Superkey https://en.wikipedia.org/wiki/Candidate_key https://en.wikipedia.org/wiki/Primary_key https://en.wikipedia.org/wiki/Compound_key

有些人使用术语“主键”来确切地表示一个整型列，它的值是由某种自动机制生成的。例如MySQL中的AUTO_INCREMENT或Microsoft SQL Server中的IDENTITY。你是在使用主键吗?

如果是，答案取决于您使用的数据库的品牌。在MySQL中，你不能这样做，你会得到一个错误:

mysql> create table foo (
  id int primary key auto_increment, 
  id2 int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

在其他一些数据库中，您可以在一个表中定义多个自动生成列。