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).