Menu
I have 3 tables and they all have innodb engine:
video(url, title, desc, country,...) url -> primary key videoCat(_url, category) {_url,category} -> primary key favorite(fav_url, thumb_path) fav_url -> primary key then I do:
alter table favorite add foreign key(fav_url) references video(url) on delete cascade and everything goes smooth, but when I try:
alter table videoCat add foreign key(_url) references video(url) on delete cascade I get:
1452 - Cannot add or update a child row: a foreign key constraint fails (
bascelik_lookaroundyou.<result 2 when explaining filename '#sql-efa_1a6e91a'>, CONSTRAINT#sql-efa_1a6e91a_ibfk_1FOREIGN KEY (_url) REFERENCESvideo(url) ON DELETE CASCADE)
why???
p.s. I am using phpmyadmin ver. 3.3.9.2
983
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB. Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
We can add a FOREIGN KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement.
To add a foreign key, click the last row in the Foreign Key Name list. Enter a name for the foreign key and select the column or columns that you wish to index by checking the column name in the Column list. You can remove a column from the index by removing the check mark from the appropriate column.
The table videoCat has one or more rows that violates the foreign key constraint. This is usually that you have a row with a value for _url that does not exist in the table video.
You can check for this with the following query:
SELECT videoCat._url FROM videoCat LEFT JOIN video ON videoCat._url = video.url WHERE video.url IS NULL EDIT
Per request, here's a query to delete those pesky rows:
DELETE FROM videoCat WHERE NOT EXISTS ( SELECT * FROM video WHERE url = videoCat._url ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
