Running SQL Commands
Often, when using databases you’ll be faced with the need to modify a table. This is straightforward. Let’s say your database looks like that illustrated in Figure 3.
)
Figure 3 An extra row in the database
Notice that the same two rows are duplicated in the USERS table. So, let’s try to delete one of the duplicate rows using the SQL DELETE command. Click the Command menu in the DBManager application and select the DELETE option. Then, enter the following text:
DELETE FROM users where USER_ID=3;
Does this delete row 3? I’m afraid it doesn’t. Figure 4 illustrates the result of trying to delete row 3.
){kind=small}
Figure 4 Integrity constraint violation
Why can’t we delete the row? The reason is revealed if we look at the schema back in Listing 5. Remember, row 4 has a foreign key relationship with another row—in this case, the related row is row 3. So, to delete row 3 we must first delete row 4. In fact, we can do the removal in one step as follows:
DELETE FROM users where USER_ID=4; DELETE FROM users where USER_ID=3;
Figure 5 illustrates the database after this command.
)
Figure 5 The newly modified database
Notice in Figure 5, the two related rows are now gone. Sometimes, you need a more radical deletion option in the form of dropping a table.