Why We Need Integrity Tests

Handling constraints within the database has the advantage of obtaining universal compliance. With Postgres, it becomes impossible for data from any source to violate the constraints we’ve put in place. On the other hand, some constraints do not readily lend themselves to being handled by the database. Here, we discuss some types of difficult constraints to look out for. We then consider both preventative and diagnostic out-of-database approaches to data integrity.

Complex constraints

Some conditions, even on individual fields, can get complicated. A field storing an email address should be formatted as a valid email. Similarly, a phone number, an address, and an identification number could all have patterns that need to match. These can probably be done with regular expressions, which are implemented in Postgres, but not in SQLite, unless a special add-on is installed. In order to avoid back-end dependency, it may be better to check ...