SQL: remove records with invalid foreign keys

Question

I just found a bug in my Rails app which would lead to certain records not getting deleted with their associated objects. The bug has been fixed, but I now have several thousand records in my database with foreign key values pointing to records that don't exist.

Is it possible to delete all records in a table where an integer foreign key references a record in another table that doesn't exist in SQL? How?

Answer 1

delete from child_table where not exists 
   (select 1 from parent_table where parent_table.id=child_table.related_id)

the next step is of course to create a FOREIGN KEY constraint immediately (unless you're on MySQL MyISAM in which case you're out of luck on that).

Answer 2

Delete from ChildTable where ForeignKeyField not in (select PrimaryKeyField from ParentTable)

Answer 3

yes - use a compound statement using "WHERE col not in (selecy id from FKTABLE) "

Answer 4

DELETE FROM tblPerson
WHERE DepartmentID NOT IN (SELECT ID FROM tblDepartment)

Answer 5

I don't know if there is a straight forward way. But you can write a script that handles that. Say your model is like this

class Post 
 belongs_to :user
end

Here,you can use a script like this

 Post.all.each do |post|
   post.delete if post.user.nil?
 end

Its easier and straigtforward then writing SQL. But this time take a DB dump before you try and do the magic