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
After this lesson you can
- Remove rows with DELETE, in an order the foreign keys allow
Key takeaways
DELETE FROMremoves the rows itsWHEREmatches. Without aWHEREit tries to remove every row, and there's no undo.- Test the
WHEREwith aSELECTfirst, then changeSELECT *intoDELETEand keep theWHEREas 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
Discontinuedcolumn.
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 itsOrderIdand itsCustomerId.OrderDetails: one row per order line, with theOrderIdit belongs to, aProductIdand aQuantity. ItsOrderIdis 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 fromOrderDetails. - Then delete order
10251itself fromOrders. - Keep orders
10248,10249and10250, 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.