How to avoid duplicities in data (using indexes)?

Kit4it · October 23, 2014, 10:44pm

You should prevent users to add duplicated records into the database, especially in parent and linking tables.
You can achieve it using index, appended to the database table.

##Example##

Let´s have entity ProductCathegory with a field Name. (There are names of cathegories of products stored in).

Create the index:
Right click on the entity ProductCathegory and Create > New Index.
In the opened form set the Name, e.g. to ix_ProductCathegory and set the IsUnique attribute to True.
Save it.
Now you can see created index ix_ProductCathegory in the Model browser tree (in folder Indexes).
Add the field:
On the index ix_ProductCathegory create New > Index Field.
In the form set in Field the name of column, that you want to be indexed. In this case select from list field Name.
(Note, that the name of index changed to 0_Name after selection of Field Name).
Save it and deploy index to database (using Deployment Script Generator).

You should see this in the Model Browser:

Now, if user wants enter duplicated name of product cathegory, the user interface returns message: Could not add ProductCathegory. You entered duplicate data and don´t save the record.

Using combined index for more columns in the same entity

You can use the same process for avoiding duplicated combinations of two or more columns, e.g. referenced fields in linking tables.

Let´s have entity SalesOrder with columns refProductId and refProducerId.

On the entity SalesOrder create New > Index (call it e.g. ix_SalesOrder. Don´t forget to set the attribute IsUnique to value True.
First on this newly created index create New > Index Field for the field refProductId.
Second on the same index create the second New > Index Field for the field refProducerId selected.
Save it and deploy into the database.

Now the applicatin prevents the user from adding duplicated combination of product and producer.