A table constraint is a restriction (a form of data quality) that applies to
- a combination of data values in different columns of the same record (single-record or intra-record constraint), for example: "End date must be later than start date"; or
- a combination of data values from multiple records within the same table or across tables, (multi-record or inter-record constraint), for example: "A passenger cannot participate in two tours that overlap in time".
Restrictive table constraints
A restrictive table constraint checks each value or combination of values that a user submits and that the restriction applies to. If the value or values violate the restriction, processing is blocked and the table constraint gives an error message.
Example
"A passenger cannot participate in two tours that overlap in time."
This constraint is automatically evaluated whenever any of the columns referred to by the constraint is manipulated directly by a user, or indirectly by a corrective constraint on behalf of the user. This will happen, for example, when a passenger books a second tour, but also when the organisation makes a change in the start date or end date of any tour.
Corrective table constraints
A corrective table constraint automatically performs some operation that causes a combination of data values to meet the restriction. This way, the table constraint guarantees that there are no data values that are a violation against the restriction.
A corrective domain constraint typically operates in silence: it does not give a message. If you do specify a message, this message will show as a message of type Warning (and not: Error, as is the case with restrictive constraints) each time that the table constraint corrects a value entered.
Example
"If a duration for a tour is not given or not known, the tour must have a default duration of a week (7 days)".
Defining a restrictive table constraint
To define a restrictive table constraint:
1. Find the Constraints, Table Constraints node in the Modeller and Rules Engine tab of the Project Catalog.
2. Right-click on this node and choose New Table Constraint from the context menu. Alternatively, you can choose Define, Constraints, Table from the main menu.
The Constraints window is opened.
3. Minimally, in the Constraint Name field, enter a unique name for the constraint, and in the Message field, enter the error message that you want to pass to the user when input values violate the restriction.
4. In the Statement field, formulate the SQL statement that defines the restriction. Do this by writing a SELECT statement that would retrieve the combination of records and values that are prohibited because of the restriction, Once the constraint is active, it will guarantee that this combination will not actually occur. You do not need to specify anything in the SELECT clause since you are not interested in values that are not allowed in the first place. However, you will need the SELECT clause if you want to use variable values in your constraint message .
Example 1
To define a constraint that gives an error if a tour's end date is earlier than (or equal to) its start date, write:
SELECT ''
FROM tour
WHERE end_date <= start_dateExample 2
To define a constraint that gives an error if a passenger participates in two tours that overlap in time, write:
SELECT ''
FROM passenger p
, tour t1
, tour t2
RELATE p "PARTICIPATES_IN" t1
, p "PARTICIPATES_IN" t2
WHERE t2.start_date > t1.start_date
AND t2.start_date < t1.end_date 5. Click the Check button.
If the table constraint is correct, the checker will set the Correct = Yes flag.
If the constraint is not correct, a message is shown with an asterisk (*) at the position of the syntax error. Correct your work and repeat Steps 4 and 5 until the constraint is correct.
This step is essential. Even if your constraint has Active = Yes, it will NOT have an effect as long as Correct = No. When you create a .CON flatfile you are warned about the existence of incorrect constraints, but when you run from repository, as you are likely to do in Development, this behaviour can be confusing.
6. Click Save to confirm.
You can switch constraints off by setting Active = No. This can be practical for quick testing and debugging, but there is no automation: you need to remind yourself to switch the constraint back on. Try not to release any inactive constraints. Even in Development, keep inactive constraints in Development to a minimum: switch them back on as soon as possible.
Defining a corrective table constraint
To define a corrective table constraint:
1. Find the Constraints, Table Constraints node in the Modeller and Rules Engine tab of the Project Catalog.
2. Right-click on this node and choose New Table Constraint from the context menu. Alternatively, you can choose Define, Constraints, Table from the main menu.
The Constraints window is opened.
3. Minimally, enter a unique name for the constraint in the Constraint Name field.
4. Now, in the Statement field, formulate the SQL statement that defines the restriction. Do this by writing a INSERT, UPDATE or DELETE statement that corrects any combination of records and values that would otherwise be prohibited because of the restriction. This way, the constraint guarantees that such violations will not actually occur once the constraint is active.
Example 1
To define a constraint that guarantees, for any tour for which the duration is not given or not known, that a default duration of a week (7 days) applies, write:
UPDATE tour
SET return_date = start_date + 7
WHERE return_date IS NULLExample 2
A tour operator wants to qualify each passenger older than 70 automatically as a special needs customer. Write:
UPDATE passenger p
SET p.special_needs = 'Y'
WHERE 70 <
(
SELECT FLOOR(
( b.book_date -
USFormatUSFormat.CharToDate( b.birth_date, 'DD-MON-YYYY' )
) / 365
)
FROM booking b
WHERE b.id = p.booking_id
)5. Click the Check button.
If the table constraint is correct, the checker will set the Correct = Yes flag.
If the constraint is not correct, a message is shown with an asterisk (*) at the position of the syntax error. Correct your work and repeat Steps 4 and 5 until the constraint is correct.
This step is essential. Even if your constraint has Active = Yes, it will NOT have an effect as long as Correct = No. When you create a .CON flatfile you are warned about the existence of incorrect constraints, but when you run from repository, as you are likely to do in Development, this behaviour can be confusing.
6. Click Save to confirm.
You can switch constraints off by setting Active = No. This can be practical for quick testing and debugging, but there is no automation: you need to remind yourself to switch the constraint back on. Try not to release any inactive constraints. Even in Development, keep inactive constraints in Development to a minimum: switch them back on as soon as possible.
INVOKE constraints
USoft also allows you to define constraints executed by a Java or .NET component action. These constraints use the non-SQL, USoft-specific INVOKE keyword.
Like SQL-style constraints, INVOKE constraints ultimately guarantee some aspect of data integrity or integrity of behaviour. Also, like their SQL-style counterparts, each has either a restrictive or a corrective effect.
For examples of INVOKE constraints, go to the Using RDMI help topic.
Sign up for the USoft Community