mysql - 无法创建约束键

Question

我有这 3 个 MySQL 表:

应用程序:

CREATE TABLE IF NOT EXISTS `apps` (
    `identifier` INT NOT NULL AUTO_INCREMENT,
    `id` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NULL,
    `description` VARCHAR(255) NULL,
    `cloud` VARCHAR(255) NULL,
    `onpremise` VARCHAR(255) NULL,
    `type` VARCHAR(255) NULL,
    `editor` VARCHAR(255) NULL,
    `provider` VARCHAR(255) NULL,
    `administrators` INT NULL,
    PRIMARY KEY(`identifier`),

    CONSTRAINT admin_ref_team
    FOREIGN KEY(`administrators`) REFERENCES `teams`(`identifier`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

质量:

CREATE TABLE IF NOT EXISTS `quality` (
    `identifier` INT NOT NULL AUTO_INCREMENT,
    `quality` VARCHAR(255) NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    PRIMARY KEY(`identifier`)
);

应用与质量的关联:

CREATE TABLE IF NOT EXISTS `assoc_apps_quality` (
    `identifier` INT NOT NULL AUTO_INCREMENT,
    `apps_id` VARCHAR(255) NOT NULL,
    `quality_id` INT NOT NULL,

    PRIMARY KEY(`identifier`),
    CONSTRAINT apps_ref_quality
    FOREIGN KEY(`apps_id`) REFERENCES `apps`(`identifier`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

    CONSTRAINT quality_ref_apps
    FOREIGN KEY(`quality_id`) REFERENCES `quality`(`identifier`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

应用程序和质量已创建，但第三个表给我这个错误，我无法创建它:

Error in query (1215): Cannot add foreign key constraint

我不明白问题出在哪里。约束名称在所有数据库中是唯一的，并且没有拼写错误。有什么想法吗？

Answer 1

您在“quality_ref_apps”中定义的apps_id错误，请尝试以下sql。

CREATE TABLE IF NOT EXISTS `assoc_apps_quality` (
`identifier` INT NOT NULL AUTO_INCREMENT,
`apps_id` INT NOT NULL,
`quality_id` INT NOT NULL,

PRIMARY KEY(`identifier`),
CONSTRAINT apps_ref_quality
FOREIGN KEY(`apps_id`) REFERENCES `apps`(`identifier`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,

CONSTRAINT quality_ref_apps
FOREIGN KEY(`quality_id`) REFERENCES `quality`(`identifier`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);

Change from apps_id VARCHAR(255) NOT NULL, TO apps_id INT NOT NULL,