Erkman Erkman - 23 days ago 9
MySQL Question

mysql #1215 - Cannot add foreign key constraint - but why?

we have the following script:

CREATE TABLE `dataBodyChanges` (
`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`numberOfContainers` int( 11 ) NOT NULL ,
`quantityPerContainer` int( 11 ) NOT NULL ,
`grossWeight` double NOT NULL ,
`comment` varchar( 1000 ) NOT NULL ,
`file` int( 10 ) unsigned DEFAULT NULL ,
`user` int( 10 ) unsigned NOT NULL ,
`dataBodyId` int( 10 ) unsigned NOT NULL ,
`created_at` timestamp NULL DEFAULT NULL ,
`updated_at` timestamp NULL DEFAULT NULL ,
PRIMARY KEY ( `id` ) ,
KEY `databodychanges_databodyid_foreign` ( `dataBodyId` ) ,
CONSTRAINT `databodychanges_databodyid_foreign` FOREIGN KEY ( `dataBodyId` ) REFERENCES `dataBodies` ( `id` )
) ENGINE = InnoDB AUTO_INCREMENT =1113 DEFAULT CHARSET = utf8;


with mysql output:

1215 - Cannot add foreign key constraint



the other table is:

USE material;

CREATE TABLE `dataBodies` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`partNumber` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`totalOrderQuantity` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`containerPart` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`numberOfContainers` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`quantityPerContainer` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`kanbanNumber` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`matHandlingCode` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`grossWeight` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`partRemarks` varchar(18) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI11Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI12Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI13Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI14Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI15Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI16Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`AI17Z` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`filler` varchar(163) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`dataHeaderId` int(10) unsigned DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `databodies_dataheaderid_foreign` (`dataHeaderId`),
CONSTRAINT `databodies_dataheaderid_foreign` FOREIGN KEY (`dataHeaderId`) REFERENCES `dataHeaders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


We do not know why....

can anybody please help who is master in this?
Thank you in advance!

Answer Source

When you are creating tables, it will check if dependent table is already created, if not it will throw that error.

For which you first need to set,

set foreign_key_checks = false; // above all

Above line will temporarily disable foreign key checks if any.

Then run above query. It will work. then after executing your queries, enable it by,

set foreign_key_checks = true;