4 / 6
SQL (SQLite engine)

Test a soft delete vs hard delete

Medium70 pts~25 min
  • Soft delete
  • Hard delete
  • Foreign keys
Practice app · Acme Commerce DB

A real SQL database (SQLite engine) seeded with an e-commerce and HR dataset, with schema browser and query runner.

Test URL
/lab/database-testing-test-a-soft-delete-vs-hard-delete

Your starter code already declares TEST_URL — never hardcode a host.

Objective

Compare the two delete strategies: soft-delete a customer with order history and hard-delete one without.

Your task

  1. 1Try a hard delete of customer 3 (who has orders): DELETE FROM customers WHERE id = 3 must fail on the foreign key.
  2. 2Soft-delete customer 3 instead: set status to 'inactive'.
  3. 3Hard-delete customer 13, who has no orders.
  4. 4Verify: SELECT id, status FROM customers WHERE id IN (3, 13) returns only (3, 'inactive').

Acceptance criteria

  • The hard delete of customer 3 fails with FOREIGN KEY constraint failed
  • Customer 3 is inactive and customer 13 is gone
  • The last result set is (3, 'inactive')

Constraints

  • 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.

SQL (SQLite engine) · Database Testing · CRUD & data integrity