Code reviewMediumcr-g212

Subject Missing indexLevel Mid–Senior~24 minCommon in Databases & SQL interviewsIndustries Software development, Technology

Question

Review this SQL. Deleting a single customer takes 40 seconds and locks up the orders table.

What a strong answer looks like

Separate real bugs from style. Rank issues by severity, point at the root cause rather than the symptom, and suggest a concrete fix — specific and kind.

Learn the concepts

Talk through your review

Code to reviewsql

1CREATE TABLE orders (2  id          BIGSERIAL PRIMARY KEY,3  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,4  total       NUMERIC,5  created     TIMESTAMPTZ6);7-- orders has 200M rows; the only index is the primary key.8 9DELETE FROM customers WHERE id = $1;

Run or narrate your approach, then ask the coach.