Wikiadmin: Created page with "== How to add a constraint == The generic documentation about Constraints in Openbravo can be found here. === Objective === The HowTo on Ho..."

2018-10-14T13:49:15Z

Created page with "== How to add a constraint == The generic documentation about Constraints in Openbravo can be found here. === Objective === The HowTo on Ho..."

New page

== How to add a constraint ==

The generic documentation about Constraints in Openbravo can be found [[Constraints_and_Triggers | here]].

=== Objective ===

The HowTo on [[How_to_add_Columns_to_a_Table | 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 [http://wiki.openbravo.com/wiki/How_to_create_build_validations_and_module_scripts 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.

[[Image:HowToAddAConstraint1.png|center]]

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 [[Database_Model/org.openbravo.model.ad.ui/AD_Message | 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 [[Messages | 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:

[[Image:HowToAddAConstraint2.png|center]]

=== 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_Build_Tasks#Database_tasks|Development tasks document]].

[[Category:HowTo]]

How to add a Constraint - Revision history

Wikiadmin: Created page with "== How to add a constraint == The generic documentation about Constraints in Openbravo can be found here. === Objective === The HowTo on Ho..."