Check constraints using the Software Factory

In any application it is important to validate a user’s input.

• Is the value of the order smaller than the maximum order value?
• Is the start-date earlier than the end-date?
• Does this action lead to a stock amount smaller than zero?

There are a few options how you, as a developer, can implement these checks:

• Domain (using min/max value)
• Default
• Trigger
• Check constraint

A closer look

Let’s take the following example and work out if and how we can implement its validation:

Is the start-date earlier than the end-date?

• Domain
  • Validation through a domain is not possible in this case since we need to compare two values
• Default
  • Clear out the end-date field and send a warning to the user informing that the end-date should occur after the start date
  • Advantage: The user is being informed while the data is being entered.
  • Risk: Business rule is only being enforced on the screens where the default has been implemented. When this data is created through other means, for example a task or external interface, this rule is not automatically enforced.
• Trigger
  • Raise an error when the end-date is before the start date
  • Advantage: the business rule is being enforced on a database level, whatever the source of the modification, the rule is enforced
  • Risk: Triggers are harder to implement, being set-based (in SQL Server). Furthermore, there could be multiple validations on a certain table. This leads to extra complexity.
• Check constraint
  • Add a check constraint to the table that validates whether the start-date lies before the end-date
  • Advantage: Easier to implement. Per verification you can add a check constraint and create an appropriate error message. Furthermore, check constraints are executed before the triggers  will be, so more complex logic of triggers will not be attempted to execute.

Opinion

Any check should be enforced on the database, so always use either a check constraint or a trigger. This will lead to consistent behaviour of your application, regardless where a mutation is coming from.

In addition to enforcing your data consistency on a database level you can choose to implement a default procedure to help the user and prevent them from making a mistake. This is especially helpful on screens that are being used a lot. Be aware that implementing both a default and a trigger/check constraints leads to duplicate logic that needs to be maintained.

So, trigger or constraint, what to use?

As a guideline use a constraint when possible. When the validation is more complex, involving multiple tables, you can use a trigger to raise an error.

Check constraint implementation

I have noticed that check constraints are not being used as frequently as they could be. Perhaps because it is being tucked away a bit inside the data modeler.

Implementing a check constraint is pretty straightforward, you simply add a record and provide the expression that needs to be validated.

Wiki - Check constraint

This will create the following constraint on the database:

ALTER TABLE [dbo].[evnt]  WITH CHECK ADD  CONSTRAINT [c_evnt_till_from] CHECK  (([event_till]>[event_from]))
GO

ALTER TABLE [dbo].[evnt] CHECK CONSTRAINT [c_evnt_till_from]
GO

This will lead to a situation where the event_till date cannot be smaller than the event_from date.

Translating check constraint messages

Finally we need to be able to inform the user about their erroneous input. The default translation could suffice, but using some ‘dynamic-model-magic’ we can create a specific message for each check constraint. In order to do so, add the following bit of SQL to your dynamic model:

insert into msg (project_id
                ,project_vrs_id
                ,msg_id
                ,msg_description
                ,msg_location_id
                ,severity
                ,msg_error_code
                ,msg_regular_expression
                ,generated
                ,insert_user
                ,insert_date_time
                ,generated_by_control_proc_id
                )
select tcc.project_id
      ,tcc.project_vrs_id
      ,'err_check_constraint_' + tcc.check_constraint_id                    as msg_id
      ,'Translation for check constraint error: ' + tcc.check_constraint_id as msg_description
      ,'popup'                                                              as msg_location_id
      ,0                                                                    as severity               --error
      ,547                                                                  as msg_error_code         --error code when check constraint fails
      ,'.*"' + tcc.check_constraint_id + '".*'                              as msg_regular_expression --regex that will find the specific constraint
      ,1                                                                    as generated
      ,dbo.tsf_user()                                                       as insert_user
      ,sysdatetime()                                                        as insert_date_time
      ,@control_proc_id                                                     as generated_by_control_proc_id
  from tab_check_constraint tcc  
 where tcc.project_id = @project_id
   and tcc.project_vrs_id = @project_vrs_id
   and tcc.generated = 0 --only manual 
   and not exists (select * --message does not already exist
                     from msg m
                    where m.project_id = tcc.project_id
                      and m.project_vrs_id = tcc.project_vrs_id
                      and m.msg_id = 'err_check_constraint_' + tcc.check_constraint_id
                  )

Once you have added this bit of dynamic model code you can translate the message like any other message.