Searched Delete Statement

The syntax for the delete statement is as follows.

delete_stmt:
          DELETE [database_name.]table_name [[AS] correlation_name] 
               [WHERE {conditional_expr | CURRENT OF cursor_name}]

If no WHERE clause is specified then all of the rows in the table are deleted. If a WHERE clause is specified then only those rows for which the conditional expression is true will be deleted. If a referential integrity violation occurs on any row during the execution of the DELETE statement, then the delete fails with no rows deleted. A referential integrity violation occurs when there is a FOREIGN KEY reference to a row to be deleted and the FOREIGN KEY/REFERENCES declaration does not include ON DELETE CASCADE. All FOREIGN KEY/REFERENCES declarations that do include ON DELETE CASCADE will cause the referencing rows from those tables to be deleted as well.

Our antiquarian bookshop has a limited first edition, first impression copy of Jacob's Room by Virginia Woolf worth 32,500 pounds. The owner has loaned this copy to the British Library for an upcoming Virginia Woolf exhibition. Hence, it needs to be removed from the inventory. The following queries show the pertinent information from the book table as well as the entries in all the tables that reference the book.

select bookid, publ_year, price, title from book where bookid = "woolf03";
BOOKID         PUBL_YEAR          PRICE TITLE
woolf03             1922       32500.00 Jacob's room [by] Virginia Woolf.

select * from related_name where bookid = "woolf03";
BOOKID         NAME
woolf03        Hogarth Press, publisher.

select * from genre_books where bookid = "woolf03";
BOOKID         GENRE
woolf03        Psychological fiction
woolf03        Experimental fiction

select * from subjects_books where bookid = "woolf03";
BOOKID         SUBJECT
woolf03        World War, 1914-1918
woolf03        Young men
woolf03        England

select text from note natural join note_line where bookid = "woolf03";
TEXT
First edition, first impression. One of probably
   40 'A' subscribers copies.

Because all of the references to this particular book have foreign keys that specify ON DELETE CASCADE, all that is needed to remove the book and its references is to issue the following statement.

delete from book where bookid = "woolf03";

The previous four SELECT statements will now not return any results. Now suppose you want to delete the genre "Gothic fiction." You might first attempt the direct approach as follows.

delete from genres  where text = "Gothic fiction";
**** referential integrity error: row to be deleted is referenced

The referential integrity error results from the fact that the foreign key references to this table are by default ON DELETE RESTRICT which prevents the deletion of rows from a table where references exist. The genres table is referenced by only one other foreign key: the genre column of the genres_books table. You can use the following query to list all of the rows in genres_books that reference "Gothic fiction."

select * from genres_books where genre = "Gothic fiction";
BOOKID         GENRE
austen06       Gothic fiction

There is only one reference which is Jane Austen's Northanger Abbey. So to delete "Gothic fiction" from the genres table you must first delete the reference in genres_books (which is appropriate considering the book is not gothic fiction but is, in fact, a parody of gothic fiction).

delete from genres_books where genre = "Gothic fiction";
**** 1 rows affected
delete from genres where text = "Gothic fiction";
**** 1 rows affected

At this point, since these are only examples, I suggest that you issue a rollback to restore the database back to its original state.

select * from genres where text = "Gothic fiction";
TEXT

select * from genres_books where genre = "Gothic fiction";
BOOKID         GENRE

rollback;

select * from genres where text = "Gothic fiction";
TEXT
Gothic fiction

select * from genres_books where genre = "Gothic fiction";
BOOKID         GENRE
austen06       Gothic fiction