David TG David TG - 28 days ago 13
MySQL Question

2 columns pointing to the same foreign field

I have a mySQL table

messages
with this fields:
id
,
parent
,
profesor
,
message
.

parent
and
profesor
are both users and are stored in the
user
table (
id
,
name
,
last name
)

So I created the table in phpMyAdmin and then I wrote:

alter table messaged add foreign key (profesor) references user (id)


and worked perfectly!
when I do the same for the parent, I get this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`school`.`#sql-1e30_fe`, CONSTRAINT `#sql-1e30_fe_ibfk_2` FOREIGN KEY (`parent`) REFERENCES `user` (`id`))


What I'm doing wrong?

Answer

Do you have any data in 'messages' table? If so, all the messages.parent fields should be filled with corresponding values of user.id, otherwise you get this error.

You can check not constraining rows with this query:

SELECT id, parent FROM messages WHERE parent NOT IN 
(SELECT id FROM user)