ADR: Relational Database Constraints
Status
Approved (November 2023)
Context
The relational DB's responsibility is keeping data and its integrity to the extent of its abilities. That means not allowing invalid data according to its domain and deleting data as it becomes invalid.
It has already happened that we've found nonsensical data on DB. Once introduced, this data is difficult to locate and remove and causes unexpected errors on app function and DB migrations.
While constraints can impact performance, this is not a deterrent but something to consider when deciding when and how we implement. There are several paths we can take to improve DB performance without diminishing its responsibilities.
Although we don't delete anything for now, if we do at some point, we rather control deletion from the application instead of the database. So we don't want database to cascade deletes.
Currently there are no foreign key constraints on DB, nor unique or check constraints.
Decision
On our relational database schemas, tables:
- MUST implement
PRIMARY KEYconstraint on primary key field - MUST implement
NOT NULLconstraints on obligatory fields - MUST implement
DEFAULTvalues on relevant fields - MUST implement
UNIQUEconstraints where applicable - MUST implement
FOREIGN KEYconstraints on fields that refer to another table's primary key - MUST implement
CHECKconstraints where applicable - Database MUST be on strict SQL mode on all tables
FOREIGN KEYconstraints MUST NOT cascade on delete
Consequences
Constraints do slow down DB inserts, updates and deletes. Consider, before adding a new constraint, how big is this table in a production environment and perform tests if necessary to ensure performance is not affected in a dramatic way.
Besides, when adding new foreign keys, we'll need to deal with invalid data already on the database on a case by case basis. Take into account on project estimate.
Impact
High
Driver
@Eudald Rossell Vivo
Contributors
[Team]
Accepted Date
November 2023