Search code examples
mysqlsqlusingcascadesql-delete

How to refactor this multi-table delete statement

Can anyone suggest a cleaner method to delete rows with a one-to-many relationship in one query?

This works, but I'm not very familiar with the using clause or delete, so I don't fully understand how it works.

DELETE FROM ip_record,
            entry using ip_record 
            inner join entry 
      where ip_record.site_id = ? 
        and ip_record.ip = ? 
        and ip_record.id = entry.ip_id

I have a notion that this could be done more cleanly with a cascade, but I have an irrational fear of constraints. The DB is MySQL.

Solution

  • Yes, add the constraint as follows

    ALTER TABLE entry
    ADD CONSTRAINT constr_entry_fk_ip
    FOREIGN KEY fk_ip (ip_id) REFERENCES ip_record (id)
    ON DELETE CASCADE ON UPDATE CASCADE

    and then you can just use

    DELETE FROM ip_record
WHERE ip_record.site_id=? and ip_record.ip=?

    to do what previously required a join.