How to add a Constraint
Contents
How to add a constraint
The generic documentation about Constraints in Openbravo can be found here.
Objective
The HowTo on How to add Columns to a Table added a new valid to column to the ht_salary table.
Logically the valid to date should always be after (or identical) to the valid from date. Enforcement of this logical restriction is possible with a database constraint which is a SQL-Expression which check if the data is valid on all modification done to the data in this table.
Modularity
The changes described in this Howto are done will all be part of the module with the dbprefix HT2 so will be placed in the same module which added the valid to column in the first place.
As the constraint will be placed in the module with dbprefix HT2 as just explained but the table ht_salary is defined in another module HT the constraint name must follow the usual rule and start with EM_HT2_.
If the constraint would be added in the same module as its table, then this EM_ naming-rule would not be needed. However best practice is to let it start with the full tablename in that case to ensure its name will be unique across the database.}}
 |
Remember that in all cases the full constraint-name (like any other db-object name) is not allowed to be longer then 30 characters. |
Add constraint to database
To add the constraint execute in database the following clause:
PostgreSQL <source lang="sql"> alter table ht_salary add constraint em_ht2_ht_salary_date_chk check (em_ht2_validto>=validfrom); </source>
Oracle <source lang="sql"> ALTER TABLE HT_SALARY ADD CONSTRAINT EM_HT2_HT_SALARY_DATES_CHK CHECK (VALIDTO>=VALIDFROM) ENABLE; </source>
| |
Adding an unique constraint to an existing module is considered as an API change and could affect to existing environments already populated. Before adding it evaluate the risk and consider creating a a buildvalidation to check if the existing data complies. If it does not the buildvalidation can stop the update process and give a proper message. |
Adding a proper message
Now when editing data in the Employee Salary || Salary tab and trying to use a Valid to date lying before the Valid from date we get an error message like shown below.
However this error message isn't too useful yet for the user as it does not indicate at all why the save action was not done.
It would be better it said something like "The Valid To date cannot be before the Valid From date" to help the user to specify the two dates correctly. This is done adding a new Message. This leverage the Openbravo translation system so the message can be translated and shown in a users language.
Details on how to create a new Message entry can be found here.
As a short summary:
In the Application Dictionary || Message window create a new record using the following details:
- Module Openbravo Howtos 2 as this is the module containing the constraint also.
- Search key: The search key must be exactly the same as the constraint's one, in this case em_ht2_ht_salary_dates_chk as this is the link between the constraint and the message.
- Message type: Depending on the type the UI for the message box will be different (green for success, yellow for warning...), in our case we want a red error message box, so we select Error.
- Message text: It is the user friendly message that will be displayed inside the message box. So let's enter: The Valid To date may not be before the Valid From date.
That's all now we have a message like:
Export database
Whenever Application Dictionary or Physical database is modified, it is possible to export that information to xml files, this is the way Openbravo ERP maintains database data as part of its source code files. To do it just execute:
ant export.database
For further explanations read the Development tasks document.
