CODEWITHSUNDEEP
mysqlsql
jonepatr
jonepatr

Reputation: 7809

Mysql, clean up loose ends

Is there some nice automated way/program for cleaning up my database? I have a couple of tables with relations that in some cases just point at records that doesn't exist.

Upvotes: 0

Views: 126

Answers (1)

Peter Lang
Peter Lang

Reputation: 55514

Sounds like you are missing some Foreign Key Constraints.
Depending on the ON DELETE option, orphaned records will be deleted together with the referenced records, referencing columns set to NULL, or deleting will be rejected.

You will have to delete those existing entries manually using a query like this, before creating your constraints:

DELETE FROM table_a
WHERE ref_b IS NOT NULL
  AND NOT EXISTS ( SELECT 1 FROM table_b WHERE table_b.id = table_a.ref_b )

Upvotes: 3

Related Questions