CODEWITHSUNDEEP
mysqldatabaseforeign-keysconstraintscreation
Souad
Souad

Reputation: 5094

Can't create constraint key

I have these 3 MySQL tables:

Apps:

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
);

Quality:

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`)
);

Association of apps and quality:

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
);

Apps and Quality are created, but the third table is giving me this error and I can't create it:

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

I don't see where is the problem. The constraint names are unique among all the database and there is no typos. Any ideas ?

Upvotes: 0

Views: 345

Answers (2)

Binit Ghetiya
Binit Ghetiya

Reputation: 1979

Your apps_id defined in "quality_ref_apps" is wrong please try below 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,

Upvotes: 1

starko
starko

Reputation: 1149

Check data type. 

   FOREIGN KEY(`apps_id`) REFERENCES `apps`(`identifier`)

apps_id is VARCHAR,

identifier is INT

Upvotes: 1

Related Questions