For data validation in SQL, using SQL constraints is key. If you take validating data entered into your database, you should look beyond its data type.

For example, some columns should not be set to hold data while in other cases it should be made mandatory for data imputation. There sort of restriction implementation is what is regarded as "Constraints" in SQL.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to execute SQL queries for their Databases.

In this context we will look into Using SQL Constraints Within Transactions.

More about SQL Constraints?

As earlier stated with constraints restrictions can be implemented on transactions to control how data imputation on Database is done. In some cases, you might need to add data to a table that contains a column with a "NOT NULL" constraint.

Alternatively, you can add a record can be done by appending a blank row to your table and then insert values into it at a later time. So when you try to append a "Not Null" constraint on one column, the operation will fail.

Generally, SQL does not allow you to add a row that has a null value in a column with a "NOT NULL" constraint, even though you plan to add data to that column before your transaction ends. 

To solve this issue, SQL enables you to designate constraints as either "DEFERRABLE" or "NOT DEFERRABLE".

How to use SQL constraint in different scenarios?

Basically, Constraints that are "NOT DEFERRABLE" are applied immediately. However, you can choose to set "DEFERRABLE" constraints to be either initially "DEFERRED" or "IMMEDIATE". A "DEFERRABLE" constraint set to IMMEDIATE will act like a "NOT DEFERRABLE" constraint and applied immediately on an Operation. On the other hand, if set to "DEFERRED", then it is not enforced.

In order to append blank records or perform other operations that may violate  "DEFERRABLE" constraints, you can use a statement similar to the following'

SET CONSTRAINTS ALL DEFERRED ;

This statement puts all DEFERRABLE constraints in the DEFERRED condition. It does not affect the NOT DEFERRABLE constraints. After you have performed all operations that could violate your constraints — and the table reaches a state that does not violate them. Thus you can reapply them. To reapply your constraints, run the following statement:

SET CONSTRAINTS ALL IMMEDIATE ;

If you made a mistake and any of your constraints are still being violated, you find out as soon as this statement takes effect.

If you do not explicitly set your DEFERRED constraints to IMMEDIATE, SQL does it for you when you attempt to COMMIT your transaction. If a violation is still present at that time, the transaction does not COMMIT; instead, SQL gives you an error message.

Including a CONSTRAINT  in a table definition can help to ensure accuracy of a column.

Similarly, the SET CONSTRAINTS DEFERRED statement lets you temporarily disable or suspend all constraints, or only specified constraints. The constraints are deferred until either you execute a SET CONSTRAINTS IMMEDIATE statement or you execute a COMMIT or ROLLBACK statement. So you surround the previous two UPDATE statements with SET CONSTRAINTS statements.

Need support in executing SQL Queries? We are available to help you today.