Hi Cliff, Cliff Hirsch wrote on Monday, August 27, 2007 9:39 AM: > 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?
Well, end of the day, you can't truly do it without a lot of table locking. Depending on how critically transactional your data is, something like this would suffice: LOCK TABLE all of the potentially affected tables SELECT ID FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T2.ID=T1.ID WHERE T2.ID IS NULL (assuming T2.ID is defined as not null) Store the results of the above in a temp. table, or iterate over each in PHP and issue a DELETE statement. UNLOCK TABLE ... > Should I just check each referenced table sequentially for the parent > id? Yeah, that's essentially what needs to happen. > 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? Yeah, you could do a multi-table delete, but sometimes things get sticky. I'd start off with the above, and then you could optimize into a multi-table delete for one or more relationships (ie, test what you're doing first). > Should I split the full text search product table into an innodb > table for the keys and a myisam table for the text? That might be the best way to go. Remember, depending on how transactionally critical your data is, simulating these types of operations may not be fool-proof. At the end of the day, there's a reason database engines are written to support these types of things - consistency. Splitting the two tables might be a good way to proceed (plus you get a space savings), but remember that you can't have foreign keys between Innodb and MyISAM. Then again, with the right schema, maybe you can make the MyISAM tables strictly leaf tables, and easier to deal with. H _______________________________________________ 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
