Deleting Data

Remove rows with DELETE FROM ... WHERE, test the WHERE first, and delete in the order the foreign keys allow.

Apprentice · T-SQL · SQL · Updated

By

After this lesson you can

  • Remove rows with DELETE, in an order the foreign keys allow

Key takeaways

  • DELETE FROM removes the rows its WHERE matches. Without a WHERE it tries to remove every row, and there's no undo.
  • Test the WHERE with a SELECT first, then change SELECT * into DELETE and keep the WHERE as it was.
  • Delete in the reverse order of inserting: first the rows that point to a row, such as the order lines, then the order itself.
  • Business records are often kept, not deleted: Northwind stops selling a product by updating its Discontinued column.

DELETE removes rows from a table.

DELETE FROM <table>
WHERE <which rows>;

As with UPDATE, the WHERE decides which rows go, and without it every row goes.

Deleting what you added

Add a shipper, then remove it:

INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Rapid Delivery', '(503) 555-0110');

DELETE FROM Shippers
WHERE CompanyName = 'Rapid Delivery';
(1 row affected)

(1 row affected)

SELECT first, again

The same habit as for UPDATE: run a SELECT with the same WHERE, check the rows, then change SELECT * into DELETE:

SELECT *
FROM Shippers
WHERE ShipperId = 4;
DELETE FROM Shippers
WHERE ShipperId = 4;

Foreign keys decide the order

Try to delete product 1, Chai:

DELETE FROM Products
WHERE ProductId = 1;
The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.OrderDetails", column 'ProductId'.

Chai appears on 38 order lines, and each of them points at product 1 with a foreign key. Deleting the product would leave those order lines pointing at nothing, so the database refuses.

To delete a row that others point to, you first deal with the rows that point to it: delete them, or change them to point somewhere else. The order is the reverse of inserting:

To insert To delete
the category first, then its products the products first, then their category
the order first, then its order lines the order lines first, then the order

For real business data, like Chai and its orders, you usually don't delete at all: the sales happened, and the records matter. That's why Northwind's products have a Discontinued column: you stop selling a product by updating it, not by deleting it.

Deleting everything

DELETE FROM Customers;

Without a WHERE, this tries to delete every customer. (Here, the foreign keys from Orders stop it, but a table nothing points to would simply be emptied.) If you ever really mean to delete every row, write a WHERE that says so, such as WHERE ShipperId > 0, so it's clearly on purpose.

Do it: remove an order typed in twice

A new clerk typed in Hanari Carnes's order twice. Order 10250 is the real one; order 10251 is the copy, with the same two order lines. Remove the copy and its lines, and leave every other order as it is.

There are two tables (the Tables tab shows the SQL that makes them):

  • Orders: one row per order, with its OrderId and its CustomerId.
  • OrderDetails: one row per order line, with the OrderId it belongs to, a ProductId and a Quantity. Its OrderId is a foreign key: each line must point to an order that exists.

Here is every order and its lines:

OrderId CustomerId Its lines (ProductId × Quantity)
10248 VINET 11 × 12, 42 × 10
10249 TOMSP 14 × 9
10250 HANAR 41 × 10, 51 × 35
10251 HANAR 41 × 10, 51 × 35 (the copy)

After your SQL runs, the check lists each order that's left, with how many lines it has, sorted by OrderId. It should give exactly these rows:

OrderId Lines
10248 2
10249 1
10250 2

Your job: write the DELETE statements that remove order 10251.

  • First, delete order 10251's two lines from OrderDetails.
  • Then delete order 10251 itself from Orders.
  • Keep orders 10248, 10249 and 10250, and all their lines.

The foreign key decides that order: if you delete the order while its lines still point to it, the database refuses with FOREIGN KEY constraint failed.

Practiced live on the lesson page: write the SQL right there and check it.

Check yourself

2 questions to try on the lesson page.

So what

A DELETE can't be taken back. Test its WHERE with a SELECT, delete children before parents, and ask whether the row should really be deleted, or only marked as no longer in use.

Key terms

Cheat sheets