外键约束的形式不正确，但是外键和引用键列是相同的。

Question

我在做一个从MySQL工作台到MariaDB数据库的“反向工程师”。在执行反向工程时，尽管引用键和外键都具有标识属性和属性，但在透视表上出现了“外键约束不正确形成”的错误。

我有3张表，其中2张是独立的表，使用枢轴表有多到多的关系。这些表是users表、certificates表和users_has_certificates枢轴表。users和certificates表都使用具有标识类型的id，即BIGINT(20), NOT NULL, UNSIGNED,和AUTO_INCREMENT。但是，在生成枢轴表时，约束错误的形式是不正确的。

当运行SHOW ENGINE INNODB STATUS时，它将具体显示此错误。

LATEST FOREIGN KEY ERROR
------------------------
2021-03-15 21:09:31 0x5a8 Error in foreign key constraint of table `database_this`.`if`:

    FOREIGN KEY (`certificate_id`)
    REFERENCES `database_this`.`certificates` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/
for correct foreign key definition.
Create table `database_this`.`if` with foreign key constraint failed.
Field type or character set for column 'certificate_id' does not
mach referenced column 'id' near '
    FOREIGN KEY (`certificate_id`)
    REFERENCES `database_this`.`certificates` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci'.

下面是生成每个表的SQL代码。

users表：

CREATE TABLE IF NOT EXISTS `database_this`.`users` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_name_unique` (`name` ASC),
  UNIQUE INDEX `users_email_unique` (`email` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

certificates表：

CREATE TABLE IF NOT EXISTS `database_this`.`certificates` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `desc` TEXT NULL DEFAULT NULL,
  `certifiable_type` VARCHAR(255) NOT NULL,
  `certifiable_id` BIGINT(20) UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

users_has_certificates表：

CREATE TABLE IF NOT EXISTS `database_this`.`users_has_certificates` (
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `certificate_id` BIGINT(20) UNSIGNED NOT NULL,
  `expired_date` DATE NULL,
  INDEX `fk_users_has_certificates_certificates1_idx` (`certificate_id` ASC),
  INDEX `fk_users_has_certificates_users1_idx` (`user_id` ASC),
  CONSTRAINT `fk_users_has_certificates_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `database_this`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_certificates_certificates1`
    FOREIGN KEY (`certificate_id`)
    REFERENCES `database_this`.`certificates` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

Answer 1

显然，如果在原始数据库中已经有相似的表，那么MySQL工作台不喜欢逆向工程一些表。解决方案是删除数据库中的所有表，以便数据库为空，然后对该数据库执行反向工程。我会把这个标记为决心。