CODEWITHSUNDEEP
mysql
Korben
Korben

Reputation: 736

Mysql can not add foreign key

I have a study case where three tables has to be created. there creation statements are as following.

create table COMMUNITY(
c_id varchar(10) primary key,
name varchar(30) not null,
longitude float,
latitude float,
post_code varchar(15) not null,
key(c_id))

create table UNIT(
c_id varchar(10) not null,
u_id int not null,
name varchar(20) not null,
key(c_id, u_id),
primary key(c_id, u_id),
constraint unique(c_id, u_id),
constraint FK_UNIT foreign key(c_id) references COMMUNITY(c_id) 
on delete cascade on update cascade)



create table ROOM(
r_id int not null,
u_id int not null,
c_id varchar(10) not null,
name varchar(20),
primary key(c_id, u_id, r_id),
constraint FK_ROOM_UID foreign key(u_id) references UNIT(u_id)     
on delete cascade on update cascade,
constraint FK_ROOM_CID foreign key(c_id) references UNIT(c_id) 
on delete cascade on update cascade)

the Community and unit tables are created successfully, but when I try to create room, mysql gives me Error Code: 1215. Cannot add foreign key constraint I wonder what's going here and How I can create them? (I knew InnoDB can solve this problem, but is there any other way I can do that?)

Thanks

Upvotes: 0

Views: 32

Answers (2)

Korben
Korben

Reputation: 736

I finally fugured this out, according to the MySQL log

"Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint."

All I have to do is to create primary key at the first column of a table and make sure the type matches. so I changed my statements as following

create table UNIT(
primary key(c_id, u_id),
c_id varchar(10) not null,
u_id int not null,
name varchar(20) not null,
constraint unique(c_id, u_id),
constraint FK_UNIT foreign key(c_id) references COMMUNITY(c_id)
on delete cascade on update cascade)



create table ROOM(
primary key(c_id, u_id, r_id),
r_id int not null,
u_id int not null,
c_id varchar(10) not null,
name varchar(20),
constraint FK_ROOM_UID foreign key(c_id, u_id) 
references UNIT(c_id, u_id) on delete cascade on update cascade)

Now I can create tables properly. I have been struggling on this problem since yesterday.

Upvotes: 0

Steven Moseley
Steven Moseley

Reputation: 16355

This looks like the culprit of the actual error you're seeing:

constraint FK_ROOM_UID foreign key(u_id) references UNIT(u_id) 
constraint FK_ROOM_UID foreign key(c_id) references UNIT(c_id)

It should be:

constraint FK_ROOM_UID foreign key(c_id, u_id) references UNIT(c_id, u_id)

Referencing the double-column key in the UNIT table

You'll also need to use InnoDB to actually create the foreign key indexes, so:

CREATE TABLE UNIT(
    ....
) ENGINE=InnoDB;

Upvotes: 1

Related Questions