Test referential integrity on delete

Verify a department with employees cannot be deleted, an empty department can, and no employee is left pointing at a missing department.

1. 1Try DELETE FROM departments WHERE name = 'QA'; employees reference it, so it must fail.
2. 2Delete the empty 'Support' department; it must succeed.
3. 3Verify: SELECT COUNT(*) AS orphaned_employees FROM employees e LEFT JOIN departments d ON d.id = e.department_id WHERE e.department_id IS NOT NULL AND d.id IS NULL.

• Deleting QA fails with FOREIGN KEY constraint failed
• Only Support was removed from departments
• The last result set shows 0 orphaned employees

• Every run starts from a freshly seeded database, so your script must include its own setup statements.
• A failing statement is recorded and the script keeps running, so you can trigger an error and verify afterwards.
• Only the last result set of your script is compared, so finish with the verification query.