MySQL 术语“约束"vs“外键"不同之处?

2022-01-20 00:00:00 constraints foreign-keys mysql ddl

我正在查看 MySQL 文档 这里 并试图理清 FOREIGN KEYs 和 CONSTRAINTs 之间的区别.我认为 FK 是一个约束，但文档似乎在谈论它们时好像它们是独立的东西.

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

创建 FK 的语法是(部分)...

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

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

所以CONSTRAINT"子句是可选的.为什么要包含它或不包含它?如果你忽略它，MySQL会创建一个外键而不是一个约束吗?或者它更像是CONSTRAINT"只不过是你的 FK 的名称，所以如果你不指定它，你会得到一个匿名的 FK?

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?

任何澄清将不胜感激.

谢谢，

伊森

推荐答案

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

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

  • PRIMARY KEY:是的，作为表约束和列约束.
  • FOREIGN KEY:是的，作为表约束，但仅适用于 InnoDB 和 BDB 存储引擎；否则会被解析但被忽略.
  • CHECK:在所有存储引擎中解析但忽略.
  • UNIQUE:是的，作为表约束和列约束.
  • NOT NULL:是作为列约束.
  • DEFERRABLE 等约束属性:不支持.
  • 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.
  • DEFERRABLE and other constraint attributes: no support.

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

The CONSTRAINT clause 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 CONSTRAINT clause is optional. If you leave it out, the RDBMS creates a name automatically, and the name is up to the implementation.

相关文章