You could do it by left joining the product table to each of the child tables, then deleting the product records where all child IDs are NULL. To err on the side of safety do it in 2 steps the first time.
The 1st query would look something like SELECT p.productID, c.productID, o.productID, f.productID FROM products p LEFT JOIN categories c ON p.productID = c.productID LEFT JOIN orders o ON p.productID = o.productID LEFT JOIN favorites f ON p.productID = f.productID ORDER BY c.productID, o.productID, f.productID Those records that are null in the last 3 fields are your orphans. The order by clause should bring them to the top of the result. If the results look OK to you, you can delete product records based on the result of the first query. There are several ways to do this, e.g. using either a temporary table or a subquery. You might also want to limit the number of deletions to the number of orphans you find. Ellen Coen [EMAIL PROTECTED] -----Original Message----- From: Cliff Hirsch [mailto:[EMAIL PROTECTED] Sent: Monday, August 27, 2007 01:38 PM To: [email protected] Subject: [mysql] Simulating foreign key constraints for MyISAM engine I want to list and then delete orphaned records in a products table. By orphaned, I mean a product that is not in any categories, orders, favorites lists, etc. With InnoDB, I could just use "ON DELETE RESTRICT" to delete a record that has no foreign key relationships. But the product table needs full text search capability, so I need the MyISAM engine. How do I simulate "ON DELETE RESTRICT" foreign key check using the MyISAM engine? Should I just check each referenced table sequentially for the parent id? Is there a SQL join that would simulate the on delete restrict? Something like: Delete record from table left join child table where parentid != childid? Should I split the full text search product table into an innodb table for the keys and a myisam table for the text? Other techniques? Thanks, Cliff _______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php _______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
