Constraints
Constraints are declarative integrity rules enforced by the DBMS to maintain data consistency, validity, and relational model adherence.
Types of constraints
Domain Constraints
Domain constraints define the legal value space for each attribute its data type, format, range, and permissible values. Enforced at the attribute level during INSERT and UPDATE operations. Implemented through type systems (INTEGER, VARCHAR(n), DATE, DECIMAL(p,s)), NOT NULL to enforce mandatory attributes, CHECK predicates for conditional validation (salary > 0, age BETWEEN 18 AND 65, status IN (‘active’,‘inactive’)), pattern matching for format validation (email LIKE ’%@%.%’), and UNIQUE for attribute-level uniqueness. DEFAULT clauses provide fallback values. These constraints ensure attribute values conform to their semantic domain definitions.
Entity integrity constraints
Entity integrity mandates that primary keys uniquely identify tuples and cannot contain NULL values, ensuring every entity has definite identity within its entity set. Enforced through PRIMARY KEY constraints combining UNIQUE and NOT NULL semantics. Applies to single attributes or composite keys where the attribute combination must be unique. Violation prevention occurs at insertion and update time. Fundamental to relational model as it enables unambiguous tuple identification and forms the basis for foreign key references establishing inter-relation dependencies.
Referencial integrity constraints
Referential integrity maintains consistency across related entity sets by requiring foreign key values to match existing candidate key values in referenced relations or be NULL (if relationship is optional). Implemented via FOREIGN KEY constraints with declarative cascading actions: ON DELETE CASCADE (propagates deletions to dependent tuples), ON DELETE SET NULL (nullifies foreign keys when referenced tuple deleted), ON DELETE RESTRICT/NO ACTION (prevents deletion if dependents exist), and equivalent ON UPDATE actions for key modifications. Prevents orphaned tuples records referencing non-existent entities—and maintains relationship validity across entity sets through enforcement of inclusion dependencies.
Key constraints
Key constraints specify attribute sets that uniquely identify tuples, establishing both intra-relational identity and inter-relational linkage mechanisms. PRIMARY KEY enforces uniqueness and NOT NULL, selected from candidate keys as the designated identifier. Candidate keys are minimal super keys—attribute sets uniquely identifying tuples without redundant attributes. Super keys encompass all possible unique identifier combinations including non-minimal sets. Alternate keys are non-selected candidate keys enforced via UNIQUE constraints, providing alternative access paths. Foreign keys establish relational dependencies through FOREIGN KEY constraints referencing candidate keys in other relations. Surrogate keys are system-generated identifiers (sequences, UUIDs) without business semantics. Composite keys combine multiple attributes for uniqueness where no single attribute suffices. Compound keys are composite keys where constituent attributes are foreign keys, typical in junction tables implementing many-to-many cardinalities through associative relations.