SQLite - Delete Data
The DELETE statement can be used to delete data in a table.
The WHERE clause can be used in order to specify exactly which rows should be deleted.
You can delete all rows, some rows, or none, depending on the filtering conditions applied by the WHERE clause.
A Simple Delete
First, let's find an artist to delete:
sqlite> SELECT * FROM Artists; ArtistId ArtistName Bio -------- -------------------- ---------------------------------------- 1 Joe Satriani 2 Steve Vai 3 The Tea Party 4 Noiseworks 5 Wayne Jury 6 Mr Percival 7 Iron Maiden 8 Atmasphere Australian jazz band centred around poly 9 Ian Moss 10 Magnum 11 Strapping Young Lad 12 Slayer 13 Primus 14 Pat Metheny 15 Frank Gambale 16 Mothers of Invention 17 The Wiggles
OK, let's delete artist number 12 (Slayer).
Here's the simple delete statement that deletes that record from the Artists table:
So, after running that, we can verify that the record was in fact deleted with a SELECT statement:
sqlite> SELECT * FROM Artists WHERE ArtistId = 12; sqlite>
(No results).
Deleting Data with a Foreign Key Constraint
If you attempt to delete a record that is referenced by a foreign key, you should get an error. You will need to delete the foreign key records first, before deleting the primary key record.
For example, artist ID 17 (from the Artists table) happens to be referenced by two records in the Albums table (see the ArtistId column):
sqlite> SELECT * FROM Albums WHERE ArtistId = 17; AlbumId AlbumName ReleaseDate ArtistId -------- -------------------- ------------ ------------ 5 Yummy Yummy 1994 17 13 Big Red Car 1995 17
So if we try to delete artist 17 from the Artists table (which contains the primary key), we should get an error.
Here's the delete statement:
And here's the result:
sqlite> DELETE FROM Artists ...> WHERE ArtistId = 17; Error: FOREIGN KEY constraint failed sqlite>
So, we need to delete the foreign key records before we delete the primary key.
If you didn't get a FOREIGN KEY constraint failed message, you may need to enable foreign key constraint checking. Run PRAGMA foreign_keys = ON; to enable foreign key constraint checks.
Delete Associated Records
So let's delete the associated records in the Albums table, before trying the above delete statement again.
Check that they were deleted:
sqlite> SELECT * FROM Albums WHERE ArtistId = 17; sqlite>
(No results).
Now let's try the original delete statement again:
Looks promising — no error:
sqlite> DELETE FROM Artists ...> WHERE ArtistId = 17; sqlite>
And a SELECT statement confirms the deletion:
sqlite> SELECT * FROM Artists WHERE ArtistId = 17; sqlite>
No results.
Delete all Rows
You can delete all rows in a table by omitting the WHERE clause.
We have a spare table (Albums1), so let's try it on that one.
First, let's just check that it actually contains data:
sqlite> SELECT * FROM Albums1; AlbumId AlbumName Year ArtistId -------- -------------------- ------------ ------------ 1 Killers 1981 7 2 Powerslave 1984 7 3 Surfing with the Ali 1987 1 4 Heavy as a Really He 1995 11 5 Yummy Yummy 1994 17 6 Out of the Loop 2007 6 7 Suck on This 1989 13 8 Pork Soda 1993 13 9 Sailing the Seas of 1991 13 10 Flying in a Blue Dre 1989 1 11 Black Swans and Worm 2010 1 12 Somewhere in Time 1986 7 13 Big Red Car 1995 17
It does. So let's go ahead and delete it all:
Now let's check its contents again:
sqlite> SELECT * FROM Albums1; sqlite>
(No results).