MySQL非主外键 [英] MySQL non primary foreign key

问题描述

我是一个新手，我无法把主键作为外键。对我来说，外键是为了连接一个表的两行。因此，使用 user 表中的 username 作为外键是合理的图片表格。这意味着该行中的图片属于指定的用户。然而，看来一般的做法似乎使用无意义的数字作为主要ID。此外，外键必须/应该引用主键。如果我不知道主键，但是我知道另一个唯一列，在这种情况下 username ，我将如何从另一个MySQL语句中获取主键，或者将外键指向非主键？

I'm a bit of a newbie and I can't get my head around primary keys as foreign keys. To me, foreign keys are meant to connect two rows of a table together. Therefore, it would make logical sense to use the, for example, username of the user table as a foreign key in the picture table. This means that the picture in that row belongs to the specified user. However, it appears that general practice favors using meaningless numbers as primary IDs. Furthermore the foreign key must/should refer to the primary key. What if I don't know the primary key, but I know another unique column, in this case username, how would I either get the primary key from within another MySQL statement, or alternatively have the foreign key point to a non primary key?

推荐答案

此外，外键必须/应该参考主键。如果我不知道主键，但是我知道另一个唯一的列（在这种情况下为username），我将如何从另一个MySQL语句中获取主键，或者将外键指向非主键？

Furthermore the foreign key must/should refer to the primary key. What if I don't know the primary key, but I know another unique column, in this case username, how would I either get the primary key from within another MySQL statement, or alternatively have the foreign key point to a non primary key?

是的，如果你有另一个唯一的键，你可以用外键引用它：

Yes, if you have another unique key, you can have foreign keys referencing it:

CREATE TABLE user
( userid INT NOT NULL 
, username VARCHAR(20) NOT NULL
---  other fields
, PRIMARY KEY (userid)
, UNIQUE KEY (username)
) ENGINE = InnoDB ;

CREATE TABLE picture
( pictureid INT NOT NULL 
, username VARCHAR(20) 
---  other fields
, PRIMARY KEY (pictureid)
, FOREIGN KEY (username)
    REFERENCES user(username)
) ENGINE = InnoDB ;

如果其他表中的所有外键都引用此唯一键（ username ），没有意义的ID是毫无意义的。您可以删除它，并使用户名表的 PRIMARY KEY 。

And if all foreign keys in other tables are referencing this Unique Key (username), there is no point in having a meaningless id. You can drop it and make the username the PRIMARY KEY of the table.

（)
有几点对于InnoDB表有一个自动递增的主键，即使它没有被用作参考，因为第一个主键或唯一索引是默认的表的聚类索引。对于 INSERT 和 UPDATE 语句，主char字段可能具有性能缺陷 - 但在 SELECT 查询

() There are a few points having an auto-incrementing primary key for InnoDB tables, even if it is not used as reference because the first Primary or Unique index is made by default the clustering index of the table. A primary char field may have performance drawbacks for INSERT and UPDATE statements - but perform better in SELECT queries.

有关使用什么的讨论，代理（无意义，生成）或自然键，以及关于这个问题的不同意见，请阅读： 代理vs -natural-business-keys

For a discussion regarding what to use, surrogate (meaningless, auto-generated) or natural keys, and different views on the subject, read this: surrogate-vs-natural-business-keys

这篇关于MySQL非主外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！