MySQL非主外键
我是个新手,我无法理解主键作为外键。对我来说,外键意味着将表的两行连接在一起。因此,使用user
表的username
作为picture
表中的外键是合乎逻辑的。这意味着该行中的图片属于指定用户。然而,一般做法似乎倾向于使用无意义的数字作为主要 ID。此外,外键必须/应该引用主键。如果我不知道主键,但我知道另一个唯一列(在本例中为用户名
),我该如何从另一个 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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
是的,如果你有另一个唯一键,你可以有外键引用它:
如果其他表中的所有外键都引用这个唯一键(
用户名
),那么拥有一个无意义的id是没有意义的。您可以删除它并使用户名
成为表的主键
。(编辑:)
InnoDB 表有几个点具有自动递增主键,即使它不用作参考,因为第一个主索引或唯一索引默认为表的聚集索引。主字符字段对于
INSERT
和UPDATE
语句可能存在性能缺陷 - 但在SELECT
查询中性能更好。有关使用什么、代理(无意义、自动生成)或自然键以及对该主题的不同观点的讨论,请阅读以下内容:代理与自然业务键
Yes, if you have another unique key, you can have foreign keys referencing it:
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 theusername
thePRIMARY KEY
of the table.(Edit:)
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
andUPDATE
statements - but perform better inSELECT
queries.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
使用“无意义”值作为主键的原因是“有意义”值有不时变化的趋势。
在用户被重命名的情况下,您不需要去更改其他表中的许多行。这就是为什么通常的做法是给它们一个无意义的 ID(通常是自动递增)。
The reason that you use a "meaningless" value for a primary key, is that "meaningful" values have a tendency to change from time to time.
In the case of a user being renamed, then you don't want to have to go and change many rows in other tables. This is why it's normal practice to give them a meaningless ID (typically auto-incrementing).
我认为如果在开始时使用这些列创建了索引,那么您可以将外键指向任何列(或多列)。
尝试执行
然后创建外键应该可以工作。
I think you can have the foreign key point to any column (or columns), if there is an index created with those columns at the beginning.
Try executing
and then creating your foreign key should work.