MySQL FK 的正确命名约定是什么？

What is a proper naming convention for MySQL FKs?

提问by Zombies

Being that they must be unique, what should I name FK's in a MySQL DB?

由于它们必须是唯一的，我应该在 MySQL 数据库中命名 FK 吗？

回答by Daniel Vassallo

In MySQL, there is no need to give a symbolic name to foreign key constraints. If a name is not given, InnoDB creates a unique name automatically.

在 MySQL 中，不需要为外键约束提供符号名称。如果没有给出名字，InnoDB 会自动创建一个唯一的名字。

In any case, this is the convention that I use:

无论如何，这是我使用的约定：

fk_[referencing table name]_[referenced table name]_[referencing field name]

Example:

例子：

CREATE TABLE users(
    user_id    int,
    name       varchar(100)
);

CREATE TABLE messages(
    message_id int,
    user_id    int
);

ALTER TABLE messages ADD CONSTRAINT fk_messages_users_user_id 
    FOREIGN KEY (user_id) REFERENCES users(user_id);

I try to stick with the same field names in referencing and referenced tables, as in user_idin the above example. When this is not practical, I also append the referenced field name to the foreign key name.

我尝试在引用表和被引用表中坚持使用相同的字段名称，user_id如上例所示。如果这不切实际，我还会将引用的字段名称附加到外键名称。

This naming convention allows me to "guess" the symbolic name just by looking at the table definitions, and in addition it also guarantees unique names.

这种命名约定允许我仅通过查看表定义来“猜测”符号名称，此外它还保证名称的唯一性。

回答by lorenzo

my choice is different. in my opinion, a table should have an idfield, not a user_idone, because table is just called user, so:

我的选择是不同的。在我看来，一张表应该有一个id字段，而不是一个字段user_id，因为 table 只是被调用user，所以：

CREATE TABLE users(
   id    int,
   name       varchar(100)
);

CREATE TABLE messages(
   id int,
   user_id    int
);

user_idin messagestable is a fk field so it has to make clear which id is (user_id).

user_idin messagestable 是一个 fk 字段，所以它必须明确哪个 id 是 ( user_id)。

a fully-self-explaining naming convention, in my opinion, could be:

在我看来，一个完全不言自明的命名约定可能是：

fk_[referencing table name]_[referencing field name]_[referenced table name]_[referenced field name]

i.e.: `fk_messages_user_id_users_id`

note:

笔记：

• you can, in some case, omit the second element ([referencing field name])

• this fk could is unique, because if a messages_usertable exists, the referencing field name should be user_id(and not just id) and the fk name should be:

  fk_messages_user_user_id_users_id

• 在某些情况下，您可以省略第二个元素（[引用字段名称]）

• 这个 fk 可能是唯一的，因为如果一个messages_user表存在，引用字段名称应该是user_id（而不仅仅是id）并且 fk 名称应该是：

  fk_messages_user_user_id_users_id

in other words, a foreign key naming convention make you sure about unique names if you also use a "referencing/referenced field" naming convention (and you can choose your own, of course).

换句话说，如果您还使用“引用/引用字段”命名约定（当然，您可以选择自己的），则外键命名约定可以确保唯一的名称。

回答by user12345

If you don't find yourself referencing fk's that often after they are created, one option is to keep it simple and let MySQL do the naming for you (as Daniel Vassallo mentions in the beginning of his answer).

如果您发现自己在创建 fk 后没有经常引用它们，那么一种选择是保持简单并让 MySQL 为您命名（正如Daniel Vassallo 在他的回答开头提到的那样）。

While you won't be able to uniquely "guess" the constraint names with this method - you can easily find the foreign key constraint name by running a query:

虽然您无法使用此方法唯一地“猜测”约束名称 - 您可以通过运行查询轻松找到外键约束名称：

use information_schema;
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA = 'your_db_schema_name' ORDER BY TABLE_NAME;

For example you might receive the following from the query:

例如，您可能会从查询中收到以下信息：

+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| note       | taskid      | note_ibfk_2     | task                  | id                     |
| note       | userid      | note_ibfk_1     | user                  | id                     |
| task       | userid      | task_ibfk_1     | user                  | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+

If this extra step isn't too much for you, then you should be able to easily find the fk you are looking for.

如果这个额外的步骤对你来说不是太多，那么你应该能够轻松找到你正在寻找的 fk。

回答by V?n Quy?t

fk-[referencing_table]-[referencing_field]

The reason is the combination of referencing_tableand referencing_fieldis unique in a database. This way make the foreign key name easy to read, for example:

原因是组合referencing_table并referencing_field在数据库中是唯一的。这种方式使外键名称易于阅读，例如：

table `user`:
    id
    name
    role

table `article`:
    id
    content
    created_user_id /* --> user.id */
    reviewed_user_id /* --> user.id */

So we have two foreign keys:

所以我们有两个外键：

fk-article-created_user_id
fk-article-reviewed_user_id

Adding usertable name to foreign key name is reduntdant.

将user表名添加到外键名是多余的。