Test a composite unique index
Hard120 pts~45 min
- Composite keys
- Unique indexes
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.
Your starter code already declares TEST_URL — never hardcode a host.
Objective
Add a composite unique index so a product appears at most once per order, then prove a duplicate line is rejected.
Your task
- 1Confirm the seed has no duplicates: GROUP BY order_id, product_id HAVING COUNT(*) > 1 returns nothing.
- 2CREATE UNIQUE INDEX ux_order_items_order_product ON order_items (order_id, product_id).
- 3Insert a copy of order_items id 1 (same order_id and product_id, quantity 1); it must fail.
Acceptance criteria
- order_items has a unique index on (order_id, product_id)
- The duplicate line fails with UNIQUE constraint failed
- The script uses CREATE UNIQUE INDEX
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.
SQL (SQLite engine) · Database Testing · Constraints & keys