Deleting Data from Tables
When we want to completely remove data from our tables, we need a SQL query. This role is filled by the DELETE keyword. If we want to remove a user, remove products from our catalog, or move some data to another table, we can easily delete the data from its existing location. The basic syntax is as follows:
DELETE FROM TableName
WHERE SomeColumn operator value;
As we mentioned, tables that have explicit foreign key references established can set the ON DELETE CASCADE properties on the foreign keys. In this case, our previous DELETE statement would cause any rows in dependant tables (for instance, those that declared us as the parent table) that had their foreign key value equal to the key in the row we just deleted to be deleted automatically.
When we have not set up cascading deletes with foreign keys, we must do the work ourselves. If we wanted to delete the forum with the name "Fans of Britney Spears" in our message board system, we could execute the following (assuming we had found out that this forum had a forum_id of 134):
DELETE FROM Forums
WHERE forum_id = 134;
However, we now have a situation where any messages in the Messages table that were posted to this forum are invalid. We could then delete those messages with the following query:
DELETE FROM Messages
WHERE forum_id = 134;
It is worth noting that this might not be the best application design. Users might find the sudden loss of the messages disturbing, and they might have wanted to keep some of their content. Finding a way to put them in a private folder for the user might keep them happier.
To delete all the rows in a table (dangerous!), we execute the following:
DELETE FROM TableName;
After rows have been deleted, they are gone. While some database servers have started to introduce the concept of undoing certain actions (including DELETE queries), these are still in a minority, and you should not count on this. Thus, you are urged to be extra cautious with DELETE statements and never use them without a WHERE sub-clause.
The currently connected user needs to have the DELETE privilege to perform this action.
| 