shopware onetonone relationship error :General error: 1215 Cannot add foreign key constraint

Question

I am trying to add 2 Tables to my database using these 2 queries, but I get this error on adding OnetoOne relationship, I am using shopware as framework to execute this queries. The error says :

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint"

CREATE TABLE `texdata_productFields` (
`id` BINARY(16) NOT NULL,
`product_id` BINARY(16) NOT NULL,
`product_version_id` BINARY(16) NOT NULL,
`form_number` INT(11) NOT NULL,
`quality_number` INT(11) NOT NULL,
`color_number` INT(11) NOT NULL,
`color_group` INT(11) NULL,
`variant_number` INT(11) NOT NULL,
`size_value` VARCHAR(255) NOT NULL,
`size_format` VARCHAR(255) NULL,
`color_rgb_code` VARCHAR(255) NULL,
`color_group_rgb_code` VARCHAR(255) NULL,
`main_article_number` VARCHAR(255) NULL,
`care_symbols` VARCHAR(255) NULL,
`created_at` DATETIME(3) NOT NULL,
`updated_at` DATETIME(3) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `json.texdata_productFields.translated` CHECK (JSON_VALID(`translated`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `texdata_productFields_translation` (
`materials` VARCHAR(255) NULL,
`colorGroupName` VARCHAR(255) NULL,
`variantDescription` VARCHAR(255) NULL,
`freiText1` VARCHAR(255) NULL,
`freiText2` VARCHAR(255) NULL,
`freiText3` VARCHAR(255) NULL,
`created_at` DATETIME(3) NOT NULL,
`updated_at` DATETIME(3) NULL,
`texdata_productFields_id` BINARY(16) NOT NULL,
`language_id` BINARY(16) NOT NULL,
PRIMARY KEY (`texdata_productFields_id`,`language_id`),
KEY `fk.texdata_productFields_translation.texdata_productFields_id` (`texdata_productFields_id`),
KEY `fk.texdata_productFields_translation.language_id` (`language_id`),
CONSTRAINT `fk.texdata_productFields_translation.texdata_productFields_id` FOREIGN KEY (`texdata_productFields_id`) REFERENCES `texdata_productFields` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk.texdata_productFields_translation.language_id` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Language Table is a default table, this is the query that builds it:

CREATE TABLE `language` (
`id` BINARY(16) NOT NULL,
`parent_id` BINARY(16) NULL,
`locale_id` BINARY(16) NOT NULL,
`translation_code_id` BINARY(16) NULL,
`name` VARCHAR(255) NOT NULL,
`custom_fields` JSON NULL,
`created_at` DATETIME(3) NOT NULL,
`updated_at` DATETIME(3) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `json.language.custom_fields` CHECK (JSON_VALID(`custom_fields`)),
KEY `fk.language.parent_id` (`parent_id`),
KEY `fk.language.locale_id` (`locale_id`),
KEY `fk.language.translation_code_id` (`translation_code_id`),
CONSTRAINT `fk.language.parent_id` FOREIGN KEY (`parent_id`) REFERENCES `language` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk.language.locale_id` FOREIGN KEY (`locale_id`) REFERENCES `locale` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk.language.translation_code_id` FOREIGN KEY (`translation_code_id`) REFERENCES `locale` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Answer 1

You probably need to make sure that you have set up the texdata_productFields_id and language_idcolumn in the child table to a nullable column.

If you set

ON DELETE SET NULL

To your foreign key then it won't allow you to set the field as

NOT NULL