Semantic integrity constraints enforce the logical meaning of the data and its relationships. Physical integrity constraints enforce the physical integrity of the data, for example ensuring that the bit sequence 00000001 is stored as 00000001 and not 00000010.
The emphasis of this section is on declarative semantic constraints: constraints that are part of the definition of the database. You can also implement procedural support for integrity using database features such as triggers and stored procedures. The principal drawbacks to procedural enforcement of database integrity are performance, the added programming requirements, and the increased opportunity for errors in enforcement. A declarative constraint is typically a better choice than a stored procedure.
The least favorable method for enforcing database integrity is through application programming. Besides the increased programming burden and its likely introduction of errors into integrity enforcement, application programming introduces the additional fault of application specificity. If one application enforces database integrity and another does not, or if two programs enforce integrity in different and perhaps contradictory ways, a the chance of corrupting the database is even greater. Worse, application-based database integrity cannot affect ad hoc inserts, deletes, and updates to the database, and as a result place further burdens on the DBA to find other mechanisms of preventing database corruption.
The point of relational databases is to be application-neutral, thus serving all applications equally well.
These constraints and the other constraints defined for the system during the process of normalization, such as functional and multivalued dependencies, have the added advantage of facilitating query and join optimization. The richer the constraint set specified for a database, the more opportunities there are to enhance query optimization.
- Column-level constraints
- Table-level constraints
- Database constraints
Specify constraints anywhere you can, being mindful of the performance debit their enforcement accrues. If you cannot trust your data, maintaining the databases that contain it is not worthwhile.
You can never declare semantic database constraints on columns defined with the XML, JSON, BLOB, or CLOB data types.
If performance issues make declarative constraints an unattractive option, implement integrity constraints by another mechanism. The performance savings from implementing integrity constraints outside the database are often only transferred from the database to the application, negating any actual performance gains realized by not implementing the constraints declaratively.
The most important consideration must be that database integrity is consistently enforced.