Why Can't I Delete a Job Record: Foreign Key Constraint Violation?

Error: Foreign Key Constraint Violation – Deleting Parent Rows

Attempting to delete a job record (e.g., DELETE FROM jobs WHERE job_id = 1 LIMIT 1;) results in error #1451: "Cannot delete or update a parent row: a foreign key constraint fails". This occurs because the advertisers table has a foreign key referencing the jobs table's advertiser_id.

Database Schema:

The database includes these tables:

• advertisers:

  • advertiser_id (primary key)
  • name, password, email, address, phone, fax, session_token
  • Foreign key constraint advertisers_ibfk_1 referencing jobs.advertiser_id

• jobs:

  • job_id (primary key)
  • advertiser_id (foreign key)
  • name, shortdesc, longdesc, address, time_added, active, moderated

Resolution: Temporarily Deactivate Foreign Key Constraints

The solution involves temporarily disabling foreign key checks to allow the deletion, then re-enabling them to preserve data integrity:

<code class="language-sql">SET FOREIGN_KEY_CHECKS = 0;  -- Disable foreign key checks

DELETE FROM `jobs` WHERE `job_id` = 1 LIMIT 1; -- Delete the problematic job record

SET FOREIGN_KEY_CHECKS = 1;  -- Re-enable foreign key checks</code>

This method enables the deletion while maintaining the overall database's referential integrity. Remember to carefully consider the implications of disabling foreign key checks, and only use this approach when absolutely necessary.

The above is the detailed content of Why Can't I Delete a Job Record: Foreign Key Constraint Violation?. For more information, please follow other related articles on the PHP Chinese website!