Delete query to delete entries without constraints

I have a table say table1 with 50 records, table1's records are tied up with other child tables using constraints.

Not all the 50 records have constraints, there could be few records (say 15 ) without constraints, so i want to run a delete query deleting 15 entries alon开发者_StackOverflow社区e out of total 50.

I tried delete ignore statement:

delete ignore from table1;

but it didn't help & I got this error:

Cannot delete or update a parent row: a foreign key constraint fails

What is the best possible way to accomplish this in mysql queries?

---

DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM details_table d WHERE d.table1_id = table1.id)

---

Here's a simple, readable, efficient query that will do it for you:

DELETE FROM table1
WHERE id NOT IN (
   SELECT table1_id FROM details_table_1
   UNION
   SELECT table1_id FROM details_table_2
   -- more unions to other child tables as required
);

---

I've always preferred joins to sub-queries that use IN():

http://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html

Sometimes there are other ways to test membership in a set of values than by using a subquery. Also, on some occasions, it is not only possible to rewrite a query without a subquery, but it can be more efficient to make use of some of these techniques rather than to use subqueries. One of these is the IN() construct.

...

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other modern database systems offer a wide range of outer join types.

Here's how to answer your question with LEFT OUTER JOIN:

DELETE FROM table1
LEFT OUTER JOIN child_table_1 c1 ON table1.id = c1.table_1_id
LEFT OUTER JOIN child_table_2 c2 ON table1.id = c2.table_1_id
-- More joins for additional child tables here
WHERE c1.table_1_id IS NULL  
AND   c2.table_1_id IS NULL
-- AND other child tables
;