Error Cannot add foreign key constraint MySQL Error 1215

Question

I was creating a database and I want to make relation between tables "Member" and "Group_Member". But when I make the column "Memp_Id" as a foreign Key in table "Group_Member" as the picture shown this error appear. I have checked the data type but both are the same. What I have to do now?

@mostafahashim maybe you are setting id's in GroupMember table ,that yout Membetr table doesnot have. — bumbumpaw, Jul 24 '14 at 09:31
@mostafahashim can you display the errors which you are getting setting foreign key and query you are using for setting foreign key? — Haritsinh Gohil, Dec 07 '18 at 06:12

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

Make sure they are both the same datatype and check if unsigned is the same on both, and that if you already have group_member rows then mem_id values will need to exist in the members table.

See here for more clarity of what unsigned means, What does "unsigned" in MySQL mean and when to use it?

edited May 23 '17 at 11:50

Community

1
1

answered Jul 24 '14 at 09:33

CaptRisky

751
4
13
25

yes , the table "group_Member" Is Empty , no data yet to add – mostafa hashim Jul 24 '14 at 09:37
and check if unsigned is the same on both,plz explain more – mostafa hashim Jul 24 '14 at 09:38
unsigned is an attribute u can define on columns with integer type – DarkBee Jul 24 '14 at 10:26

score 1 · Accepted Answer · answered Dec 11 '18 at 13:59

Reasons for error code 1215 cannot add foreign key constraint are listed in percona's article, but in the most cases the reasons are as follows:

child table's potential foreign key contains ids which is not present in parent table, but your child table 'Group_Member' is empty so it is not applicable for you.
The Local Key, Foreign Table or Column in the Constraint References Have a Typo
The Column the Constraint Refers to Is Not of the Same Type or Width as the Foreign Column, for e.g.if primary key has data type and width int(11) then foreign key's type and width also has to be as int(11)
Different Charsets/Collations Among the Two Table/Columns, for example if primary key has collation utf8_unicode_ci and foreign key has collation as latin1_general_ci then error will occur, so it should be the same.
The Parent Table Is Not Using InnoDB, for setting foreign key, if your parent table is of type MyISAM then it will show above error #1252 so it should be InnoDB.
The Parent Table Is Partitioned, you should check partition also.
Using SET DEFAULT for a Constraint Action, like Default NULL on PK or FK so Dont use Default on these keys.

Error Cannot add foreign key constraint MySQL Error 1215

2 Answers2