Enforcing data integrity

If you’re an agileBase administrator, you’ve probably used the ‘unique’ field option to ensure that, for example, no one enters two products with the same code, or to make sure invoice numbers can never be repeated, or to enforce the uniqueness of customer account codes (a few uses amongst many).

That takes care of the situation where one field needs to have unique values, but there are other cases where a combination of field values should be unique.

What does that mean? Well, take some other examples:

Say for example you’re managing lists of attendees for events, booking them on and cancelling if necessary. You might like to ensure that no person appears more than once as an attendee at a single event. To do that, you could add a ‘unique constraint’ covering the combination of event ID and person ID.

Another case would be if you operate a ‘multi-tenanted’ system where lots of different companies in your group can log in to the same system but with their data separated. In that case, you might not be able to set a Product Code to be unique as different companies may want to use the same code. However, you could make the combination of Company ID and Product Code unique.

In short, the more you can do to ensure your data doesn’t get in a pickle, the better.

The good news is that with a new feature released today, you can add those more advanced constraints.

In a table’s ‘manage’ tab, click the ‘unique’ icon. Select a set of fields you wish to be unique in combination by ticking them and press ‘add constraint’. You can add as many constraints as you like, covering different combinations of fields.