Deleting rows in a database

Good practice is to execute DELETE statements in a TRY/CATCH block, to handler potential SQL errors related to foreigh key constraints.

Normally a database should use foreigh key constraints, to define when a column references a row in another table, and thus enforce data consistency. For example, an order

If this tutorial, the database does not use foreign key constraints in the database. However, the BDL code is ready to check for SQL errors.

In this code example, we a TRY/CATCH block to protect the DELETE statement removing a customer row:

 1   TRY
 2      DELETE FROM customer WHERE cust_num = curr_cust
 3   CATCH
 4      ERROR SFMT("DELETE failed: %1",SQLERRMESSAGE)
 5      RETURN curr_cust
 6   END TRY

The CATCH block is executed, if an SQL error occurs. The typical SQL error that can occur here is a foreigh key constraint reference error.

Since the row cannot be deleted because it is still referenced in another table, the program must warn the user, and can then continue.

Catching SQL errors for INSERT and UPDATE statements is also a good practice, to detect foreign key constraint errors, and well as UNIQUE, CHECK and NOT NULL constraints errors.