外键首选字符串还是整数? [英] 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?

推荐答案

外键首选字符串还是整数?

Is string or int preferred for foreign keys?

视情况而定

现有的讨论关于 自然键和代理键 - 您需要决定什么适合您，以及您的组织内的标准"是什么.

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.

在 OP 的情况下，有一个代理键 (int userId) 和一个自然键 (char 或 varchar username).任一列都可以用作表的主键，无论哪种方式，您仍然可以强制另一个键的唯一性.

In the OP's case, there is 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.

以下是选择一种或另一种方式时的一些注意事项:

Here are 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，使用窄主键(如 int)比使用更宽(和可变长度)值(如 varchar)具有性能和存储优势.这个好处也会影响引用 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 be narrower (fewer bytes to fetch).

代理整数键的另一个好处是可以轻松更改用户名，而不会影响引用 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, and other tables are coupled to MyUsers via username, it makes it very 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 is needed to retain data integrity.

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

使用代理键的一个缺点是其他通过代理键引用 MyUsers 的表需要JOIN返回到 MyUsers 如果需要 Username 列，请使用表.自然键的潜在好处之一是，如果查询只需要引用 MyUsers 的表中的 Username 列，则它不需要连接回 MyUsers 来检索用户名，这将节省一些 I/O 开销.

One downside of using Surrogate Keys is that other tables which reference MyUsers via a surrogate key will need to be JOINed back to the MyUsers table if the Username column is required. 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 I/O overhead.

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