Home > Articles > Programming > Java

  • Print
  • + Share This
Running SQL Commands

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.

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.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.