MySQL MySQL术语“约束”与“外键”的区别？

MySQL terminology "constraints" vs "foreign keys" difference?

提问by Bill Karwin

I'm looking at the MySQL docs hereand trying to sort out the distinction between FOREIGN KEYs and CONSTRAINTs. I thought an FK wasa constraint, but the docs seem to talk about them like they're separate things.

我正在查看此处的 MySQL 文档，并试图理清 FOREIGN KEY 和 CONSTRAINT 之间的区别。我认为 FK是一个约束，但文档似乎在谈论它们，就像它们是独立的东西一样。

The syntax for creating an FK is (in part)...

创建 FK 的语法是（部分）...

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)

So the "CONSTRAINT" clause is optional. Why would you include it or not include it? If you leave it out does MySQL create a foreign key but not a constraint? Or is it more like a "CONSTRAINT" is nothing more than a name for you FK, so if you don't specify it you get an anonymous FK?

所以“CONSTRAINT”子句是可选的。你为什么要包括它或不包括它？如果省略它，MySQL 会创建外键而不是约束吗？或者它更像是“CONSTRAINT”只不过是您 FK 的名称，因此如果您不指定它，您将获得一个匿名 FK？

Any clarification would be greatly appreciated.

任何澄清将不胜感激。

Thanks,

谢谢，

Ethan

伊森

回答by Bill Karwin

Yes, a foreign key is a type of constraint. MySQL has uneven support for constraints:

是的，外键是一种约束。MySQL 对约束的支持不均衡：

• PRIMARY KEY: yes as table constraint and column constraint.
• FOREIGN KEY: yes as table constraint, but only with InnoDB and BDB storage engines; otherwise parsed but ignored.
• CHECK: parsed but ignored in all storage engines.
• UNIQUE: yes as table constraint and column constraint.
• NOT NULL: yes as column constraint.
• DEFERRABLEand other constraint attributes: no support.

• PRIMARY KEY: 是作为表约束和列约束。
• FOREIGN KEY: 是作为表约束，但仅适用于 InnoDB 和 BDB 存储引擎；否则会被解析但被忽略。
• CHECK: 在所有存储引擎中被解析但被忽略。
• UNIQUE: 是作为表约束和列约束。
• NOT NULL: 是作为列约束。
• DEFERRABLE和其他约束属性：不支持。

The CONSTRAINTclause allows you to name the constraint explicitly, either to make metadata more readable or else to use the name when you want to drop the constraint. The SQL standard requires that the CONSTRAINTclause is optional. If you leave it out, the RDBMS creates a name automatically, and the name is up to the implementation.

该CONSTRAINT子句允许您显式命名约束，以使元数据更具可读性，或者在您想要删除约束时使用该名称。SQL 标准要求该CONSTRAINT子句是可选的。如果省略它，RDBMS 会自动创建一个名称，名称由实现决定。

回答by Dan C.

In general (not necessary MySQL), foreign keys are constraints, but constraints are not always foreign keys. Think of primary key constraints, unique constraints etc.

一般来说（不是 MySQL 所必需的），外键是约束，但约束并不总是外键。想想主键约束、唯一约束等。

Coming back to the specific question, you are correct, omitting CONSTRAINT [symbol] part will create a FK with an auto-generated name.

回到具体问题，您是对的，省略 CONSTRAINT [symbol] 部分将创建一个具有自动生成名称的 FK。

回答by RxBx

As of now, our CREATE TABLE DDLs are of this format - notice the UNIQUE KEY and FOREIGN KEY definition syntax we have used.

到目前为止，我们的 CREATE TABLE DDL 是这种格式 - 注意我们使用的 UNIQUE KEY 和 FOREIGN KEY 定义语法。

CREATE TABLE my_dbschema.my_table (
    id INT unsigned auto_increment PRIMARY KEY,
    account_nbr INT NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    active_flg CHAR(1) NOT NULL DEFAULT 'Y',
    vendor_nbr INT NOT NULL,
    create_ts TIMESTAMP NOT NULL DEFAULT current_timestamp,
    create_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    last_upd_ts TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    last_upd_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    UNIQUE KEY uk1_my_table(account_nbr, account_name),
    FOREIGN KEY fk1_my_table(vendor_nbr) REFERENCES vendor(vendor_nbr)
    );

In this format, MySQL is creating INDEX-es with the names uk1_my_table and fk1_my_table automatically; but the FK object name is something different - my_table_ibfk_1 (ie. tablename_ibfk_N – system defined) . So ALTER TABLE my_table DROP FOREIGN KEY fk1_my_tablewon't work (and hence frustrating and raising alarms), as there's no FK db object by that name.

在这种格式中，MySQL 会自动创建名称为 uk1_my_table 和 fk1_my_table 的 INDEX-es；但 FK 对象名称有所不同 - my_table_ibfk_1（即 tablename_ibfk_N - 系统定义）。所以ALTER TABLE my_table DROP FOREIGN KEY fk1_my_table不会工作（因此令人沮丧并引发警报），因为该名称没有 FK db 对象。

Here's an alternative DDL format wrt the constarints (Ref : https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html) :-

这是constarints的替代DDL格式（参考：https: //dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html ）：-

CREATE TABLE my_dbschema.my_table (
    id INT unsigned auto_increment PRIMARY KEY,
    account_nbr INT NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    active_flg CHAR(1) NOT NULL DEFAULT 'Y',
    vendor_nbr INT NOT NULL,
    create_ts TIMESTAMP NOT NULL DEFAULT current_timestamp,
    create_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    last_upd_ts TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    last_upd_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    CONSTRAINT uk1_my_table UNIQUE KEY (account_nbr, account_name),
    CONSTRAINT fk1_my_table FOREIGN KEY (vendor_nbr) REFERENCES vendor(vendor_nbr)
    );

In this format, MySQL is still creating INDEX-es with the names uk1_my_table and fk1_my_table automatically, but the FK object name is not something different – it's fk1_my_table as mentioned in the DDL. So ALTER TABLE my_table DROP FOREIGN KEY fk1_my_tableworks, but leaves behind the namesake INDEX.

在这种格式中，MySQL 仍然会自动创建名称为 uk1_my_table 和 fk1_my_table 的 INDEX-es，但 FK 对象名称并没有什么不同——它是 DDL 中提到的 fk1_my_table。如此ALTER TABLE my_table DROP FOREIGN KEY fk1_my_table有效，但留下了同名的 INDEX。

And, note that ALTER TABLE my_table DROP INDEX fk1_my_tablewon't work initially (when the FK is not yet dropped), with an error message that it is being used in a FK! If the DROP FK command has been executed successfully, only then the DROP INDEX works.

并且，请注意它ALTER TABLE my_table DROP INDEX fk1_my_table最初不起作用（当 FK 尚未删除时），并显示一条错误消息，表明它正在 FK 中使用！如果 DROP FK 命令已成功执行，则 DROP INDEX 才起作用。

Hope this explains and helps resolve the confusion.

希望这可以解释并帮助解决混淆。

回答by Mario Juárez

If I'm not wrong, the constraints need indexes, so when you create, for example, a foreign key constraint MySQL automatically creates an index too.

如果我没记错的话，约束需要索引，所以当你创建外键约束时，MySQL 也会自动创建一个索引。

回答by Mario Juárez

Can't answer for MySQL but FK's are constraints. Anything that forces your data into a certain condition is a constraint. There are several kinds of constraints, Unique, Primary Key, Check and Foreign Keys are all constraints. Maybe MySQL has others.

无法回答 MySQL，但 FK 是限制条件。任何迫使您的数据进入特定条件的东西都是一种约束。有几种约束，Unique、Primary Key、Check 和Foreign Keys 都是约束。也许 MySQL 有其他的。

Sometimes words are allowed in commands but not required sheerly for readability like the FROM in the DELETE statement.

有时命令中允许使用单词，但不是像 DELETE 语句中的 FROM 那样纯粹为了可读性而必需的。

回答by Johann

This is probably the most confusing topìc in MySQL.

这可能是 MySQL 中最令人困惑的话题。

Many people say that, for instance, the 'PRIMARY KEY', the 'FOREIGN KEY', and the 'UNIQUE' key are actually indexes! (MySQL official documentation is included here)

很多人说，例如，“PRIMARY KEY”、“FOREIGN KEY”和“UNIQUE”键实际上是索引！（此处包含 MySQL 官方文档）

Many others, on the other hand, say that they're rather constraints (which does make sense, cause when you use them, you're really imposing restrictions on the affected columns).

另一方面，许多其他人说它们是相当的约束（这确实有道理，因为当您使用它们时，您实际上是在对受影响的列施加限制）。

If they really are indexes, then what's the point in using the constraint clausule in order to give it a name, since you're supposed to be able to use the name of that index when you created it?

如果它们确实是索引，那么使用约束子句为其命名有什么意义，因为您应该能够在创建索引时使用该索引的名称？

Example:

例子：

... FOREIGN KEY index_name (col_name1, col_name2, ...)

... FOREIGN KEY index_name (col_name1, col_name2, ...)

If FOREIGN KEY is an index, then we should be able to use the index_name to handle it. However, we can't.

如果 FOREIGN KEY 是一个索引，那么我们应该能够使用 index_name 来处理它。然而，我们不能。

But if they're not indexes but actual contraints which do use indexes to work, then this does make sense.

但是如果它们不是索引而是使用索引来工作的实际约束，那么这确实有意义。

In any case, we don't know. Actually, nobody seems to know.

无论如何，我们不知道。实际上，似乎没有人知道。