MySQL DB 忽略外键约束

Question

我遇到了一个非常奇怪(而且可怕)的问题:我的 mySQL 数据库通常忽略外键约束!

我的所有表都是InnoDB，我使用 liquibase 创建它们，如下所示:

<addForeignKeyConstraint baseColumnNames="user_account_id"
    baseTableName="account_transaction"
    constraintName="fk_account_transaction_user_account_id"
    referencedColumnNames="id"
    referencedTableName="user_account"/>

外键创建得很好。在 HeidiSQL 中我看到它们是这样的:

我还检查了 FOREIGN_KEY_CHECKS 并将其设置为 1 (SET FOREIGN_KEY_CHECKS=1;)

我的所有 ID 列的数据类型都是 BIGINT(不知道这是否重要)。

还值得一提的是:以下声明

  SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE();

返回一堆约束，但没有外键约束...当我在本地 mySQL 数据库上执行相同的语句时，它返回所有约束，包括外键约束。我无法弄清楚它们之间有什么区别......

非常感谢任何帮助:)

@更新:所以 MyISAM 和 InnoDB 的提示就成功了。我之前确实调查过。但是，由于某种原因，我的表是 InnoDB，但我的数据库是 MyISAM。我将对此进行进一步调查:)

Answer 1

正如 Bill Karwin 在评论中指出的那样，如果表不是 InnoDB，则外键约束将被忽略。

演示:

CREATE TABLE foo_p
( id BIGINT UNSIGNED PRIMARY KEY
) ENGINE=MYISAM
;  

CREATE TABLE foo_c
( id   BIGINT UNSIGNED PRIMARY KEY
, p_id BIGINT UNSIGNED
) ENGINE=MYISAM
;

添加外键约束:

ALTER TABLE foo_c 
  ADD CONSTRAINT fk_foo_c_foo_p
  FOREIGN KEY (id) REFERENCES foo_p (id)
  ON DELETE NO ACTION ON UPDATE NO ACTION
;  

MySQL 创建一个索引，但不创建外键。

将存储引擎更改为InnoDB不会创建外键:

ALTER TABLE foo_p ENGINE=INNODB ;

ALTER TABLE foo_c ENGINE=INNODB ;

使用SHOW CREATE TABLE foo_c查看表定义:

CREATE TABLE `foo_c` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `p_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_foo_c_foo_p` (`id`)
) ENGINE=INNODB

有一个索引(从 MyISAM 定义转换而来)，但没有外键。

添加外键:

ALTER TABLE foo_c 
  DROP KEY fk_foo_c_foo_p  
, ADD CONSTRAINT fk_foo_c_foo_p
  FOREIGN KEY (p_id) REFERENCES foo_p (id)
  ON DELETE NO ACTION ON UPDATE NO ACTION
;

SHOW CREATE TABLE 显示 FOREIGN KEY 约束以及自动创建的索引:

CREATE TABLE `foo_c` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `p_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_foo_c_foo_p` (`p_id`),
  CONSTRAINT `fk_foo_c_foo_p` FOREIGN KEY (`p_id`) REFERENCES `foo_p` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB