Relational Data Modeling - (Integrity) (Constraints|action assertions)
About
Constraints are a set of rule inside a relational database that declare consistency rules in order to:
- enforce data integrity
- and give information on the data (used by the query optimizer)
Every enterprise constrains behavior in some way, and this is closely related to constraints on what data may or may not be updated. To prevent a record from being made is, in many cases, to prevent an action from taking place.
But even at one-third the size, nine thousand lines, being able to recite the poem is impressive: at one second per line, the verses would take two and one-half hours to recite. It is impressive even allowing for the fact that the poem is re-created as opposed to memorized, because neither the singer nor the audience expect word-for-word accuracy (nor would either have any way of verifying that). Most of us do not learn epic poems. But we do make use of strong constraints that serve to simplify what must be retained in memory.
Articles Related
Type
Three basic types of database integrity constraints are:
- Entity integrity, allowing no two rows to have the same identity within a table.
- Domain integrity, restricting data to predefined data types, e.g.: dates.
- Referential integrity, requiring the existence of a related row in another table, e.g. a customer for a given customer ID.
Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database.
Purpose
Constraints are used for these purposes:
- Data Quality
Constraints verify that the data conforms to a basic level of data consistency and correctness, preventing the introduction of dirty data. They are the key part to ensure referential integrity.
- Query optimization
The Database utilizes constraints when optimizing SQL queries. Although constraints can be useful in many aspects of query optimization, constraints are particularly important for query rewrite. (for instance on materialized views. See query rewriting)