Deleting data from a table is even easier than adding or updating dataperhaps too easy. You use the SQL DELETE statement to delete data. The statement takes only two parametersone required and one optional:
The DELETE statement is dangerously easy to use. Look at the following line of code (but don't execute it): DELETE FROM Directors This statement removes all directors from the Directors table, and does it without any warnings or confirmation. TIP Some databases, in particular client/server databases (such as Microsoft SQL Server and Oracle), offer safeguards against accidental or malicious deletions. There generally are two approaches to preventing mass deletion. One is to create a trigger (a piece of code that runs on the server when specific operations occur) that verifies every DELETE statement and blocks any DELETE without a WHERE clause. A second is to restrict the use of DELETE without a WHERE clause based on login name. Only certain users, usually those with administrative rights, are granted permission to execute DELETE without a WHERE clause. Any other user attempting a mass DELETE will receive an error message, and the operation will abort. Not all database systems support these techniques. Consult the database administrator's manuals to ascertain which safeguards are available to you. The DELETE statement is most often used with a WHERE clause. For example, the following SQL statement deletes a single director (the one you just added) from the Directors table: DELETE FROM Directors WHERE DirectorID=14 To verify that the row was deleted, retrieve all the Directors one last time (as seen in Figure 7.6). Figure 7.6. Most databases delete rows immediately, as opposed to flagging them for deletion. This this will be reflected when listing the table contents.As with all WHERE clauses, the DELETE statement's WHERE clause can be a SELECT statement that retrieves the list of rows to delete. If you do use a SELECT statement for a WHERE clause, be careful to test the SELECT statement first to ensure that it retrieves all the values you want, and only those values. TIP Feel free to INSERT, UPDATE, and DELETE rows as necessary, but when you're finished either clean up the changes or just copy overwrite the data file with the original (to restore it to its original state). NOTE Primary key values are never reused. If you INSERT rows after you have performed delete operations, the new rows will be assigned brand-new IDs, and the old (deleted) IDs will not be reused. This behavior is a required part of how relational databases work, and was explained in Chapter 5, "Building the Databases." |