Restrictive Constraints

A restrictive constraint prevents invalid data from being entered in the database. For example:

•In travel, you can define a restrictive constraint to ensure that a tour booking date is always earlier than the tour start date, and that the tour start date is always earlier than the tour end date.

•In banking, you can define a restrictive constraint to ensure that only valid bank account numbers can be entered. This is necessary if the individual digits in the bank account number must satisfy some proof.

You define each restrictive constraint as a SELECT statement. The first example could be written as:

SELECT ''

FROM reservation r

, schedtour st

WHERE r.schedtour_id = st.schedtour_id

AND r.book_date >= st.start_date

As the example shows, in a restrictive constraint, it is not relevant what data you select. What is relevant is what type of record is not allowed in the database. For this reason, the data in the SELECT clause is typically empty, as the example shows. But if you want to pass values of the rejected record as variables into the error message, you must select these values in the constraint's SELECT clause. For this technique, see the help topic on Variable Constraint Messages in USoft Definer.

When you define a restrictive constraint, you may specify a message. This is optional, but it is NOT good practice to create a restrictive constraint without a message. Each time the constraint selects a record that is being manipulated either directly by a user or indirectly by some corrective constraint triggered by user action, this represents a violation of the business rule implemented by the constraint. Therefore, in such a case, the message is shown to the user in the form of an error message.