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 November 3, 2026

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.

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

Deleting Data