Liran Atli Liran Atli - 3 months ago 17
MySQL Question

SQL Error (1215): Cannot add foreign key constraint

CREATE TABLE `profilePic` (
`ClientID` VARCHAR(255) NOT NULL,
PRIMARY KEY (`ClientID`),
CONSTRAINT `FK__user_details` FOREIGN KEY (`ClientID`) REFERENCES `user_details` (`ClientID`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;


I am trying to add table with foreign key and I got this error, why that happend ?


  1. trying doing new table.

  2. i am trying to put same details on
    user_details->ClientID
    and
    profilePic->ClientID

    3.i have already one table call`d userdb and in this table i have ClientID and its foreign key and its work.


Answer

The below will fail because the collation is different. Why do I show this? Because the OP didn't.

Note I shrunk the size due to error 1071 on sizing for varchar 255 with that collation and then auto chosen charset.

The point being, if collation is different, it won't work.

CREATE TABLE `user_details` (
    `ClientID` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`ClientID`)
)ENGINE=InnoDB;

CREATE TABLE `profilePic` (
    `ClientID` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`ClientID`),
    CONSTRAINT `FK__user_details` FOREIGN KEY (`ClientID`) REFERENCES `user_details` (`ClientID`) ON UPDATE CASCADE ON DELETE CASCADE
)COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.