外键首选使用string还是int吗? [英] Is string or int preferred for foreign keys?

问题描述

我有一个用户表，其中有userid和username列，两者都是唯一的.

I have a user table with userid and username columns, and both are unique.

在userid和username之间，最好用作外键，为什么?
我的老板想使用字符串，可以吗?

Between userid and username, which would be better to use as a foreign key and why?
My Boss wants to use string, is that ok?

推荐答案

看起来您同时具有代理键(int userId)和自然键(char或varchar username).任一列都可以用作表的主键，无论哪种方式，您仍然可以强制另一个键的唯一性.

It looks like you have both a surrogate key (int userId) and a natural key (char or varchar username). Either column can be used as a Primary key for the table, and either way, you will still be able to enforce uniqueness of the other key.

关于自然键和替代键之间的取舍问题，目前已有许多讨论-您将需要确定哪种方法对您有用，以及组织内的标准"是什么.

There are many existing discussions on the trade-offs between Natural and Surrogate Keys - you will need to decide on what works for you, and what the 'standard' is within your organisation.

在选择一种或另一种方式时，有一些注意事项:

Here's some considerations when choosing one way or the other:

使用代理键的情况(例如UserId INT AUTO_INCREMENT)

如果您使用代理(例如UserId INT AUTO_INCREMENT)作为主键，则所有引用表MyUsers的表都应使用UserId作为外键.

If you use a surrogate, (e.g. UserId INT AUTO_INCREMENT) as the Primary Key, then all tables referencing table MyUsers should then use UserId as the Foreign Key.

不过，您仍然可以通过使用其他唯一索引，来强制username列的唯一性，例如:

You can still however enforce uniqueness of the username column through use of an additional unique index, e.g.:

CREATE TABLE `MyUsers` (
  `userId` int NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  ... other columns
  PRIMARY KEY(`userId`),
  UNIQUE KEY UQ_UserName (`username`)

按照@Dagon，与使用更宽(且长度可变)的值(如varchar)相比，使用窄的主键(如int)在性能和存储方面具有优势.此优点还会影响引用MyUsers的其他表，因为userid的外键将变窄.

As per @Dagon, using a narrow primary key (like an int) has performance and storage benefits over using a wider (and variable length) value like varchar. This benefit also impacts further tables which reference MyUsers, as the foreign key to userid will narrower.

代理整数键的另一个好处是，可以轻松更改用户名，而不会影响引用MyUsers的表. 如果username用作自然键，则表通过username耦合到MyUsers，这使得更改用户名更加不便(因为否则会违反外键关系).如果需要在使用username作为外键的表上更新用户名，则需要使用类似 ON UPDATE CASCADE 的技术用于保持数据完整性.

Another benefit of the surrogate integer key is that the username can be changed easily without affecting tables referencing MyUsers. If the username was used as a natural key, then tables were coupled to MyUsers via username, it makes it more inconvenient to change a username (since the Foreign Key relationship would otherwise be violated). If updating usernames was required on tables using username as the foreign key, a technique like ON UPDATE CASCADE would need to be employed to retain data integrity.

使用自然键(即用户名)的情况

在使用代理键的不利方面，通过代理键引用MyUsers的其他表将始终需要join返回到MyUsers表以检索用户名.自然键的潜在好处之一是，如果查询仅需要引用MyUsers的表中的Username列，则它无需联接回MyUsers即可检索用户名，这将节省一些开销.

On the down side for using Surrogate Keys, other tables which reference MyUsers via a surrogate key will always require a join back to the MyUsers table to retrieve the username. One of the potential benefits of Natural keys is that if a query requires only the Username column from a table referencing MyUsers, that it need not join back to MyUsers to retrieve the user name, which will save some overhead.

有关自然与代理人辩论和权衡的其他参考资料此处和

Further references on the natural vs surrogate debate and trade-offs here and here

这篇关于外键首选使用string还是int吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！