SQL: deleting records with invalid foreign keys

Question

I just found an error in my Rails application that would cause certain entries to not be deleted using related objects. The bug is fixed, but now I have several thousand records in my database with foreign key values ​​pointing to records that do not exist.

Is it possible to delete all records in a table where an integer foreign key refers to a record in another table that does not 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, of course, is to immediately create a FOREIGN KEY constraint (if you are not using MySQL MyISAM, then you are out of luck).

Answer 2

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

Answer 3

, . , users id posts user_id, . :

DELETE FROM posts WHERE user_id NOT IN (SELECT id FROM users);

. , zzzeek.

, . .

Answer 4

ChildTable, ForeignKeyField ( PrimaryKeyField ParentTable)

Answer 5

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

Answer 6

I do not know if there is a direct path. But you can write a script that handles this. Say your model looks 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 simpler and harder than writing SQL. But this time, take a database dump before trying to do the magic