produce an error Error Code: 1215. Cannot add foreign key constraint in mysql

Question

i made 2 tables like this,

CREATE TABLE personal (
id INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
created_at DATETIME);


CREATE TABLE personal_details (
id INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
personal_id INT(255),
tittle VARCHAR(10) NOT NULL,
family_name VARCHAR(50) NOT NULL, 
given_name VARCHAR(50) NOT NULL,
place_of_birth VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
gender VARCHAR(25) NOT NULL,
country_citizen VARCHAR(50) NOT NULL,
national_identity_number INT(50) NOT NULL,
passport_no INT(50) NOT NULL,
issue_date DATE NOT NULL,
expiry_date DATE NOT NULL,
FOREIGN KEY (personal_id) REFERENCES personal(id)
);

but it returns an error like this Error Code: 1215. Cannot add foreign key constraint

even tough i already follow this solution from another question

https://stackoverflow.com/a/16969116/17067132

`INT` is not the same as `INT UNSIGNED`. You can't make a foreign key from one that references the other. Change one or the other, so they are the same type. — Bill Karwin, Apr 26 '22 at 16:08
Also you should remove all the "length" arguments from your `INT` columns. They don't mean anything, and they are deprecated in MySQL 8.0. See my answer to https://stackoverflow.com/a/14573482/20860 — Bill Karwin, Apr 26 '22 at 16:09

score 0 · Accepted Answer · answered Apr 26 '22 at 16:07

I am a bit confused aboud the use scholarship_uiii in between the two CREATE-statements.

But the reason for the error is that you are missing the UNSIGNED for your personal_id field. So your second CREATE-statement should read:

CREATE TABLE personal_details (
id INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
personal_id INT(255) UNSIGNED,
tittle VARCHAR(10) NOT NULL,
family_name VARCHAR(50) NOT NULL, 
given_name VARCHAR(50) NOT NULL,
place_of_birth VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
gender VARCHAR(25) NOT NULL,
country_citizen VARCHAR(50) NOT NULL,
national_identity_number INT(50) NOT NULL,
passport_no INT(50) NOT NULL,
issue_date DATE NOT NULL,
expiry_date DATE NOT NULL,
FOREIGN KEY (personal_id) REFERENCES personal(id)
);

produce an error Error Code: 1215. Cannot add foreign key constraint in mysql

1 Answers1