SQL: Constraints Integrity Guide
Enforce domain, entity, and referential integrity across relational schemas using NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and cascading actions.
1. What are SQL Constraints?
SQL constraints are declarative rules enforced by the relational database management system (RDBMS) on table columns. They act as automatic gatekeepers, rejecting any INSERT or UPDATE statement that violates the defined business rules or structural expectations.
Without database-level constraints, application bugs, concurrent race conditions, or manual administrative scripts can insert corrupt, orphan, or duplicate data. Constraints establish four core integrity levels:
| Integrity Level | Enforced By | Purpose |
|---|---|---|
| Domain Integrity | DATA TYPE, NOT NULL, CHECK, DEFAULT | Ensures column values conform to defined ranges, formats, and non-empty expectations. |
| Entity Integrity | PRIMARY KEY, UNIQUE | Guarantees that each row represents a distinct, identifiable business entity with zero duplicates. |
| Referential Integrity | FOREIGN KEY | Maintains valid associations between related tables, preventing orphaned child records. |
| User-Defined Integrity | CHECK, TRIGGERS | Validates organization-specific business rules across single or multiple columns. |
Column-Level vs Table-Level Syntax
You can define constraints at two syntactic levels during table creation:
- Column-Level Constraints:Declared immediately after the column data type (for example, email VARCHAR(100) NOT NULL UNIQUE). Ideal for constraints referencing a single column.
- Table-Level Constraints:Declared as standalone comma-separated clauses at the end of the CREATE TABLE statement. Required when constraints involve multiple columns (like composite keys).
- Explicit Constraint Naming:Always supply explicit names using CONSTRAINT constraint_name. Named constraints produce readable error messages and simplify future schema alterations.
2. NOT NULL Constraint
By default, relational columns accept NULL values unless explicitly configured otherwise. The NOT NULL constraint ensures that a column must always contain an actual value for every record.
Enforcing Mandatory Columns with NOT NULL
SQLRequire values for first and last names while leaving middle name optional
NULL vs Empty String Distinction
Do not confuse NULL with empty or zero values:
- Empty String (''):An empty string is a valid textual value with length 0. In MySQL and PostgreSQL, an empty string satisfies a NOT NULL constraint.
- Zero (0):Zero is a concrete numeric value. An integer column with NOT NULL accepts 0 without error.
- Oracle Exception:Oracle treats empty strings as NULL automatically, meaning an empty string triggers a NOT NULL violation in Oracle databases.
3. UNIQUE Constraint
The UNIQUE constraint guarantees that all non-null values stored in a column (or a group of columns) are strictly distinct across the table. When you declare a UNIQUE constraint, the database engine creates a unique B-Tree index under the hood to accelerate duplicate checks.
Preventing Duplicates with UNIQUE Constraints
SQLEnsure unique usernames, emails, and phone numbers across all user accounts
How UNIQUE Treats NULL Across Database Engines
Because NULL represents an unknown value, engines treat multiple NULLs differently:
- ANSI Standard (PostgreSQL, MySQL, SQLite, Oracle):Two NULL values are not considered equal. Therefore, a UNIQUE column permits multiple records with NULL values.
- SQL Server (Legacy Behavior):SQL Server historically allowed only a single NULL in a UNIQUE column. Modern SQL Server allows multiple NULLs via filtered unique indexes: WHERE column IS NOT NULL.
- Combined NOT NULL + UNIQUE:Combining both constraints guarantees exactly one non-empty, non-duplicate value per record (such as account email or taxpayer ID).
4. PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table. Under relational theory, every valid table must possess exactly one primary key.
A primary key automatically combines the rules of NOT NULL and UNIQUE into a single definition. Furthermore, major engines (like MySQL InnoDB and SQL Server) store the physical table records clustered directly in the order of the primary key index.
Primary Key and Table Creation
SQLConstruct an academic catalog table with a text primary key and verify stored rows
Surrogate Keys vs Natural Keys
Choosing the right identifier for your table schema:
- Natural Keys:Identifiers that exist in the real world (such as Social Security Numbers, VIN numbers, or ISO country codes). Changing real-world values requires updating every foreign key reference.
- Surrogate Keys:System-generated sequential integers or UUIDs created specifically for the database. Immune to real-world business changes and offer predictable query performance.
- Clustered Index Advantage:Sequential surrogate keys (like auto-incrementing integers) prevent index page fragmentation during heavy concurrent insertions.
5. FOREIGN KEY Constraint
The FOREIGN KEY constraint enforces a link between data in two tables, maintaining referential integrity across your database.
A foreign key in a child table points to the primary key (or unique key) of a parent table. The database engine prevents you from inserting a child record whose foreign key does not match an existing parent record, eliminating orphan data.
FOREIGN KEY Constraint and Relational Linkages
SQLEstablish parent-child relationships and verify referential joins
Nullable Foreign Keys
Understanding how foreign keys handle missing associations:
- Optional Relationships:A foreign key column can be nullable. An employee might not belong to any department yet (department_id IS NULL). The constraint allows NULL without error.
- Mandatory Relationships:To require an association, combine NOT NULL with FOREIGN KEY (for example, every order line item MUST reference a valid order_id).
- Foreign Key Indexing:Always create an explicit index on foreign key columns. The database engine does not automatically index child foreign keys in most engines, causing table scans during JOIN queries.
6. Primary Key vs Foreign Key: Direct Comparison
Primary keys and foreign keys collaborate to build relational associations. The table below contrasts their roles and characteristics:
| Evaluation Criteria | PRIMARY KEY | FOREIGN KEY |
|---|---|---|
| Primary Role | Uniquely identifies records within its own table | References the primary key of another related table |
| Quantity Allowed | Exactly one primary key per table | Multiple foreign keys allowed per table |
| NULL Acceptance | Never accepts NULL values | Accepts NULL values unless defined NOT NULL |
| Duplicate Values | No duplicates permitted (strictly unique) | Accepts duplicate values (one-to-many relationships) |
| Default Index | Automatically creates unique index (clustered by default) | Does not automatically create an index in most engines |
| Table Placement | Defined on the parent entity table | Defined on the child dependent table |
Self-Referencing Foreign Keys
A table can reference its own primary key to model hierarchical trees:
- Organizational Charts:An employees table where manager_id is a foreign key pointing to employee_id in the exact same table.
- Category Trees:An ecommerce categories table where parent_category_id references category_id to represent nested subcategories.
- Top-Level Root Nodes:The root company executive or top-level department contains a NULL manager_id value to mark the hierarchy root.
7. Composite Primary Key
A composite primary key (also known as a compound primary key) consists of two or more columns that together uniquely identify a record. While individual column values may repeat across the table, the combination of all key columns must remain strictly unique.
Composite primary keys represent standard practice in junction tables (bridge tables) modeling many-to-many relationships, such as students enrolling in courses or orders containing line item products.
Composite Primary Key in Junction Tables
SQLModel course enrollment combinations and verify uniqueness across multi-column keys
Composite Key Design Guidelines
Considerations when architecting multi-column primary keys:
- Column Order in Index:The order of columns in PRIMARY KEY (colA, colB) determines the composite B-Tree index order. Queries filtering by colA utilize the index; queries filtering only by colB cannot utilize the index prefix.
- Wide Foreign Keys:Any child table referencing a composite primary key must define matching composite foreign keys, increasing disk footprint and join complexity.
- Surrogate Key Alternative:In high-traffic web applications, teams frequently prefer a single integer/UUID surrogate primary key combined with a UNIQUE (colA, colB) constraint.
8. CHECK Constraint
The CHECK constraint enforces domain-specific business logic by testing inserted or updated values against a boolean expression. If the expression evaluates to FALSE, the database engine aborts the transaction with a constraint violation error.
CHECK Constraint Validation
SQLEnforce salary boundaries, allowed string values, and multi-column bonus logic
CHECK Constraint Logic and NULL Handling
Important rules regarding how SQL evaluates CHECK constraints:
- Three-Valued Logic:A CHECK constraint passes if the expression evaluates to TRUE or UNKNOWN (NULL). It fails only if the expression evaluates to FALSE.
- Nullable Columns:If bonus CHECK (bonus >= 0) allows NULL values, inserting NULL succeeds because NULL >= 0 evaluates to UNKNOWN. To reject NULLs, explicitly add NOT NULL.
- Deterministic Functions Only:CHECK expressions cannot query other tables, call subqueries, or invoke non-deterministic functions like CURRENT_TIMESTAMP or RANDOM().
9. DEFAULT Constraint
The DEFAULT constraint provides a fallback literal value or system expression when an INSERT statement does not specify a value for that column.
DEFAULT Constraint Fallback Values
SQLOmit optional columns during insertion to trigger automatic default values
Explicit Defaults vs NULL Overrides
Be mindful of how INSERT statements trigger or bypass defaults:
- Omitting the Column:INSERT INTO customer_orders (order_id, customer_name) VALUES (1, 'Sophia'); successfully triggers the default values for status, discount_rate, and placed_at.
- Passing Explicit NULL:INSERT INTO customer_orders (order_id, customer_name, status) VALUES (2, 'Leo', NULL); inserts NULL into status. The DEFAULT is bypassed because a value was explicitly provided.
- The DEFAULT Keyword:You can explicitly invoke the default in an INSERT statement: VALUES (3, 'Maya', DEFAULT, 0.05, DEFAULT).
10. AUTO INCREMENT (IDENTITY, SERIAL)
Relational databases provide automatic sequence counters to generate sequential integer surrogate primary keys. While the purpose is universal, the exact syntax varies across major SQL engines:
| Database Engine | Syntax Example | Underlying Mechanism |
|---|---|---|
| MySQL / MariaDB | id INT AUTO_INCREMENT PRIMARY KEY | Engine maintains internal table counter; resets upon TRUNCATE |
| PostgreSQL (Standard) | id INT GENERATED ALWAYS AS IDENTITY | Standard ANSI sequence object attached directly to column |
| PostgreSQL (Legacy) | id SERIAL PRIMARY KEY | Syntactic sugar creating a sequence and default nextval() |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY | Seed and increment step values managed by storage engine |
| SQLite | id INTEGER PRIMARY KEY AUTOINCREMENT | Utilizes internal sqlite_sequence table to prevent ID reuse |
AUTO INCREMENT Surrogate Keys
SQLGenerate sequential primary key identifiers automatically upon insertion
Sequence Gaps and ID Reuse
Sequential auto-increment generators do not guarantee consecutive gapless numbers:
- Transaction Rollbacks:If a transaction requests next ID 42 and subsequently rolls back, ID 42 is discarded and never reused. The next transaction receives ID 43.
- Crash Recovery Caches:Database servers cache sequences in memory (such as batches of 20). An abrupt server reboot leaves a gap of 20 unassigned numbers.
- Accounting Warning:Surrogate IDs should never serve as legal invoice numbers or audit sequences where strict gapless ordering is required by compliance laws.
11. ON DELETE CASCADE and ON UPDATE CASCADE
When configuring a foreign key, you must define the referential action that executes when a parent row is deleted or updated. Without explicit clauses, the default action is RESTRICT (or NO ACTION), which blocks the modification.
| Action Option | Behavior When Parent Row Modified | Recommended Use Case |
|---|---|---|
| CASCADE | Automatically deletes or updates all matching child rows in the child table. | Dependent line items (order items when an order is deleted). |
| SET NULL | Sets foreign key columns in matching child rows to NULL (requires nullable column). | Optional ownership (reassigning posts when an author account is deleted). |
| SET DEFAULT | Sets foreign key columns in matching child rows to their predefined DEFAULT value. | Reassigning tickets to a default fallback triage queue. |
| RESTRICT | Immediately blocks and aborts the parent DELETE or UPDATE statement. | Core business records (preventing customer deletion while invoices exist). |
ON DELETE CASCADE in Action
SQLDelete a parent record and observe how child records are automatically removed
The Cascading Deletion Risk
Exercise caution when configuring CASCADE across multiple relationship levels:
- Unintended Deep Deletions:A single DELETE on a top-level parent (like an organization) can cascade across 10 tables, permanently destroying thousands of orders, audit logs, and users.
- Soft Deletes as an Alternative:Many production systems prefer soft deletes (deleted_at TIMESTAMP) over ON DELETE CASCADE to preserve historical data.
- Performance Overhead:Cascading deletions trigger multiple internal table scans and lock acquisitions, creating transaction latency on high-volume tables.
12. Adding and Dropping Constraints with ALTER TABLE
Production databases frequently require adding new validation constraints to existing tables or removing obsolete constraints during architectural refactoring.
| Constraint Type | Add Syntax (PostgreSQL / MySQL) | Drop Syntax |
|---|---|---|
| PRIMARY KEY | ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id); | ALTER TABLE t DROP PRIMARY KEY; (or DROP CONSTRAINT pk_t;) |
| FOREIGN KEY | ALTER TABLE t ADD CONSTRAINT fk_ref FOREIGN KEY (p_id) REFERENCES p(id); | ALTER TABLE t DROP FOREIGN KEY fk_ref; (or DROP CONSTRAINT fk_ref;) |
| UNIQUE | ALTER TABLE t ADD CONSTRAINT uq_email UNIQUE (email); | ALTER TABLE t DROP INDEX uq_email; (or DROP CONSTRAINT uq_email;) |
| CHECK | ALTER TABLE t ADD CONSTRAINT chk_age CHECK (age >= 18); | ALTER TABLE t DROP CONSTRAINT chk_age; (or DROP CHECK chk_age;) |
| NOT NULL | ALTER TABLE t ALTER COLUMN email SET NOT NULL; | ALTER TABLE t ALTER COLUMN email DROP NOT NULL; |
Adding Constraints with ALTER TABLE
SQLExtend existing tables with new constrained columns dynamically
Validating Constraints on Existing Data
When adding constraints to a table that already contains millions of rows:
- Pre-Flight Clean Up:If any existing row violates the new constraint (like duplicate emails or negative prices), the ALTER TABLE statement immediately aborts.
- PostgreSQL NOT VALID Option:PostgreSQL allows you to add constraints without an initial full table scan lock using ADD CONSTRAINT ... NOT VALID, then validating asynchronously using VALIDATE CONSTRAINT.
- Explicit Names Required:Without explicit constraint names, dropping constraints requires querying database information_schema catalogs to discover auto-generated random names.
13. Primary Key vs Unique Key: Architectural Comparison
Both PRIMARY KEY and UNIQUE constraints enforce uniqueness across records, leading to frequent confusion among database developers. Here is how they differ architecturally:
| Architectural Factor | PRIMARY KEY | UNIQUE KEY |
|---|---|---|
| Quantity per Table | Strictly one per table | Multiple unique keys permitted per table |
| NULL Acceptance | Rejects all NULL values (implicit NOT NULL) | Accepts NULL values (engine specific count) |
| Index Structure | Creates clustered index by default in MySQL InnoDB and SQL Server | Creates non-clustered index by default |
| Foreign Key Targeting | Primary target for foreign keys across application schemas | Can serve as foreign key target, but less commonly used |
| Conceptual Role | Defines the fundamental identity of the database row | Prevents duplicate values for secondary alternate candidate keys |
Practical Schema Design Blueprint
Follow this industry design pattern for production database tables:
- Primary Key:Use a surrogate integer or UUID as the PRIMARY KEY (id) for internal joins, foreign key links, and clustering efficiency.
- Unique Keys:Apply UNIQUE constraints to natural business identifiers (such as username, email, sku, slug, or tax_identifier).
- Resulting Architecture:This architecture ensures ultra-fast join performance while strictly preserving real-world business entity uniqueness.
Knowledge Check: SQL Constraints & Data Integrity
1. How many PRIMARY KEY constraints can exist on a single SQL table?
2. What is the main difference between PRIMARY KEY and UNIQUE constraints?
3. What does ON DELETE CASCADE do when a parent row is deleted?
4. When should you implement a Composite Primary Key?
5. What condition does a CHECK constraint enforce?
6. How do you drop a named constraint in PostgreSQL or SQL Server?