How to create a unique field on a database

Question

How to ensure that a database field is unique? For example, how to avoid duplicate PO numbers?

Answer

Either create a unique index in the database, or validate the field before updating the database.

To create a database index for a unique PO:

1. Double-click the Entity to see the entity properties;
2. On the Indexes tab, create a new index, as shown below.
   • Name the index. For example, UniquePO.
   • Set the Unique property to Yes.
   • Select the attribute that should be unique. In the example, PONumber.

After the index is created, you will receive a Feedback Message if you try to create a new Order with a duplicate PO number. The message will say that you can't do the operation because the PONumber is unique and already exists in the database.

As an alternative example, validate that the PO Number is unique when you try to save an order into the database. The example below:

1. Uses an Aggregate to fetch Orders from the database, using a filter such as 'Orders.Id <> OrderForm.Record.Orders.Id'.
2. If the Aggregate is empty, the new PO Number is unique, and you can create or update the record.
3. If the aggregate is not empty, throw a warning message to the user and refuse to save the record.