AI Glossary / Data engineering and architecture / Intermediate
Integrity Constraint
Definition
An integrity constraint is a rule that defines which database states or changes are acceptable. Common examples require a value to be present, keep a key unique, restrict a value to a range, or require a reference to an existing record. Constraints help maintain consistency with declared rules. They do not guarantee that every accepted value is true in the real world or that all business requirements have been expressed.
What it means
A quantity can be required to be nonnegative, and an order can be required to reference an existing customer. These rules prevent specific classes of errors even when data arrives through different applications. They also make expectations visible to developers and data users. However, a positive quantity may still be the wrong quantity, and a valid customer identifier may still identify the wrong customer for a particular order.
How it works
Translate a business requirement into a precise condition and choose an enforcement mechanism. Database features commonly include NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. More complex rules may require additional database mechanisms or carefully designed transaction logic. Define when validation occurs and how violations are reported. Consider concurrent changes, not only one record at a time. A rule enforced only in one form can be bypassed by another import path unless the architecture consistently enforces it.
Three practical examples
- Inventory: a check prevents a recorded package count from being negative when that is the valid business rule.
- Enrollment: a unique combination of learner, offering, and attempt prevents duplicate registrations under the chosen identity model.
- AI data management: annotations reference existing source versions and require a recognized label, while separate review processes assess whether the label is substantively correct.
Think of it this way
Think of the rules of a structured form, such as requiring a date and preventing an end date before the start date. The analogy is limited because database constraints can cover relationships among records and must behave correctly under concurrent operations. A form can also accept a plausible but false statement, just as a constraint can validate structure without verifying reality.
Worked example
A toy Booking table has BookingID, RoomID, StartTime, and EndTime. A primary key prevents duplicate BookingID values. A foreign key requires RoomID to identify an existing room. NOT NULL rules require the time fields, and a check requires EndTime > StartTime. These constraints reject a booking whose end precedes its start. They do not, by themselves, prevent two different bookings from overlapping in the same room. That is a cross-row rule requiring an appropriate supported mechanism or transaction design. In PostgreSQL, a row CHECK is not the general tool for consulting arbitrary other rows. The example shows why naming a few constraints is not equivalent to fully specifying the booking policy.
Illustrative case study
Fictional example: Maple Courses validates enrollments in its website form but later adds a spreadsheet import that skips the same checks. The team moves suitable structural rules into the database and gives the importer clear exception reporting. It retains application guidance for user-friendly correction and tests the combined behavior. This invented case illustrates consistent enforcement; it does not imply that every business rule belongs in one database feature or that database errors alone make a usable interface.
Common misconceptions and mistakes
Do not confuse a constraint with a default value: a default supplies a value in some circumstances but does not necessarily restrict all supplied values. Do not assume CHECK automatically rejects NULL; SQL check semantics can allow an unknown result, so required values need explicit treatment. Avoid disabling constraints to make problematic imports appear successful without a documented validation plan. Also distinguish a declared rule from a rule that is enabled, validated for existing data, and correctly enforced by the chosen system.
Business use
Constraints provide a shared foundation for reliable data across applications, reports, and AI pipelines. Start with the most consequential invariants and express them clearly. Test valid cases, invalid cases, missing values, and competing updates. Provide actionable error handling so users can correct problems. When rules evolve, review existing data and migration behavior before changing enforcement. For AI-generated data or SQL, constraints can catch some structural mistakes, but they complement rather than replace source verification and semantic evaluation.
People also ask
Does a constraint prove that data is accurate?
No. It proves only that the enforced condition is satisfied, subject to the system's implementation.
Are all constraints row-level checks?
No. Uniqueness and referential integrity concern relationships across records, and more complex invariants may need other mechanisms.
Can an application enforce integrity rules?
Yes, but all relevant write paths and concurrency behavior must be considered; database enforcement is valuable for suitable shared invariants.
Check your understanding
A price column has CHECK (Price > 0) but no explicit required-value rule. Can you conclude missing prices are rejected?
Answer: No. In systems such as PostgreSQL, a CHECK condition that evaluates to unknown because of NULL is not a failure. Add the appropriate NOT NULL rule when every record must have a price.
Related terms
- Domain Constraint
- Primary key
- Foreign key
- Referential integrity
- NOT NULL
- CHECK constraint
- unique constraint
- transaction isolation
- data validation
- invariant
Learn next
write five rules for a small dataset and classify each as a value rule, same-row rule, cross-row rule, or cross-table rule. Select an enforcement mechanism for each and test missing values and concurrent changes. Document any rule that remains a review process rather than an automatic guarantee.
More in Data engineering and architecture
- Insertion Anomaly - An insertion anomaly is a database design problem in which a legitimate fact cannot be recorded without also supplying another, logically separate fact.
- Instruction-level parallelism - Instruction-level parallelism (ILP) is the opportunity to overlap the execution of multiple machine instructions from a program's instruction stream.
- Integrated test facility - An integrated test facility (ITF) is an audit-testing approach in which controlled test data are processed within a production system, commonly using...
- Intelligence engine - An intelligence engine is a broad label for a software component or platform that turns information into useful analytical outputs, such as ranked findings...
- Intensional Component - The intensional component of a database or knowledge system contains definitions, rules, or other knowledge that describes how information is structured or...
- Inter-record redundancy - Inter-record redundancy, in William Kent's database-design discussion, refers to redundant information across multiple record types or relations.
Sources
Sources: PostgreSQL 18, Constraints, https://www.postgresql.org/docs/18/ddl-constraints.html ; PostgreSQL 18, CREATE TABLE, https://www.postgresql.org/docs/18/sql-createtable.html ; PostgreSQL 18, ALTER TABLE, https://www.postgresql.org/docs/18/sql-altertable.html . Primary documentation search excerpts consulted for constraint categories, NULL behavior, cross-row CHECK limitations, and validation. The booking example is original and does not prescribe a complete production reservation system.
Previous: Integrity Next: Intelligence