应该将主键添加到innodb表吗？ [英] Should primary keys always be added to an innodb table?

问题描述

我有一些innodbs只有2 int列，它们是其他表的主键的外键。

I have some innoDbs with only 2 int columns which are foreign keys to the primary keys of other tables.

例如一个表是user_items，它有2列，

E.g one table is user_items, it has 2 columns, userId, itemId, both foreign keys to user and item tables, set to cascade if updated or deleted.

我应该添加第三列到这样的表，并使其成为主要的

Should I add a 3rd column to such tables and make it a primary key, or is it better the way it is right now, in terms of performance or any other benefits?

推荐答案

添加第三个ID列只是为了添加ID列没有意义。事实上，当插入或删除行时，它只是增加了处理开销（索引维护）。

Adding a third ID column just for the sake of adding an ID column makes no sense. In fact it simply adds processing overhead (index maintenance) when you insert or delete rows.

主键不一定是ID列。

A primary key is not necessarily "an ID column".

如果您只允许一个用户和项目之间关联（一个用户不能被分配两次相同的项目），那么定义 （userid，itemid）作为表的主键。

If you only allow a single associated between user and item (a user cannot be assigned the same item twice) then it does make sense to define (userid, itemid) as the primary key of your table.

如果你允许同一对出现多次，那么当然你不需要这个约束。

If you do allow the same pair to appear more than once then of course you don't need that constraint.

这篇关于应该将主键添加到innodb表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！