Data Integrity Checks

It can be important to ensure that certain rules are followed when data is entered, either manually by a user, by an incoming API request or someone filling in a chaser response.

When designing applications, just be aware there is sometimes a trade-off between the ideal of data integrity and everyday concerns.

For example, you may like to force users to always provide an email address for contacts but in a small minority of cases, they may not have that available.

Agilebase has options for handling soft validation (highlighting errors or notifying someone) as well as the hard validation (disallowing invalid data entry) options discussed here. For example, you could

• Create a viewto show all records with errors, for later checking
• Set up a workflow or chaser to chase someone when errors are found
• Use the API to send a message to your internal messaging system e.g. Slack

That said, these are the ways in which you can strictly enforce data integrity.

At an individual field level

Basic rules can be applied with field options. For example a field may be set as mandatory, or it must contain unique values (no duplicates).

Data integrity checks (advanced)

1. Edit a table
2. Click _Data Integrity Checks
3. Click ‘Add Single-Field Check’

Here you can add more complex checks, such as the value must be longer or shorter than a certain length, be within a date range or match a regular expression. The list of checks available is the same as that used in view filters.

Multi-field unique constraints

1. Edit a table
2. Click _Data Integrity Checks
3. Click ‘Add Multi-Field Unique Check’

Sometimes, fields must be unique in combination. For example, imagine you have a system for recording customer complaints. One of the fields might be a customer’s reference number, another a relation to the customer record.

You want to avoid duplicates being entered, so could make the customer reference number unique. However it could be that two different customers could use the same reference number independently, so that wouldn’t really work.

The solution is to add a multi-field unique constraint on customer and customer reference number. That way, a reference number won’t be able to be re-used for a complaint from the same customer, but more than one customer is allowed to use the same reference.