View source | Discuss this page | Page history | Printable version   

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.}}

Bulbgraph.png   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

 
ALTER TABLE ht_salary ADD constraint em_ht2_ht_salary_date_chk CHECK (em_ht2_validto>=validfrom);

Oracle

 
ALTER TABLE HT_SALARY ADD CONSTRAINT EM_HT2_HT_SALARY_DATES_CHK CHECK  (VALIDTO>=VALIDFROM) ENABLE;
Bulbgraph.png   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.


HowToAddAConstraint1.png


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:

That's all now we have a message like:


HowToAddAConstraint2.png

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.

Retrieved from "http://wiki.openbravo.com/wiki/How_to_add_a_Constraint"

This page has been accessed 7,753 times. This page was last modified on 9 February 2017, at 11:14. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.