Page 1 of 1

MySQL orphans again

Posted: Sun Jan 10, 2010 12:01 pm
by satikas
Hi, I once had this problem and thought that I solved it.
I made a script that was supposed to delete all orphaned items.
Yet it leaves some behind, so I started looking for a new solution.

I ve once again spent hours on this and found a pretty good solution:
(Provided by ralph l mayo)
http://www.codingforums.com/archive/ind ... 47680.html

I did all the steps but I cant add data into the table, because it results:

Code: Select all

Cannot add or update a child row: a foreign key constraint fails (`np19508_TIGER`.`bookmarks`, CONSTRAINT `bookmarks_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `bookmarks` (`ID`) ON DELETE CASCADE)
when doing:

Code: Select all

mysql_query("INSERT INTO bookmarks (`parent`,`ename`,`name`,`position`,`logo`,`url`,`forced`) VALUES
   ('$parent','$ename','$name','$position','$logoname','$url','no')") or die(mysql_error());
I did everything exactly except added some extra fields to parent and ID.


Also found this script, which is supposed to delete orphans, but I have no idea how to modify it to work with my ID and parent (ID values) system:

Code: Select all

This will find records in $t1
that refer to non-existant rows in $t2. Just fill in the variables.
 
SELECT t1.$key
FROM $t1 AS t1
LEFT JOIN $t2 AS t2
ON t1.$foreign_key = t2.$key
WHERE t2.$key IS NULL
Id be so grateful if I managed to get it work already.