更新时间:2023-11-28 23:09:04
外键首选字符串还是整数?
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 JOIN
ed 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.