Adding Constraints in SQL

Introduction

Adding a constraint means applying a database-enforced rule to an existing table. Constraints prevent invalid inserts and updates, protect relationships, identify rows, require values, and keep data inside an approved domain. They move essential integrity rules close to the data so every application and user follows the same contract.

ALTER TABLE employees
ADD CONSTRAINT uq_employees_email
UNIQUE (email);

Adding a constraint to a populated table is more demanding than defining one during table creation. Existing rows and all future writes must satisfy the new rule. Safe enforcement therefore begins with business definition and data profiling, continues through cleanup and application changes, and ends with controlled validation and monitoring.

Why Add Constraints?

Tables are sometimes created with minimal rules during a prototype, data import, legacy migration, or phased release. As requirements become clearer, the database may need to reject duplicate identifiers, missing names, negative prices, invalid statuses, or references to nonexistent parent rows.

Application validation alone is insufficient because several services, scripts, reports, ETL jobs, and users may write to the same database. A constraint creates one authoritative rule that applies regardless of the writer. It also documents the model for developers and tools.

Integrity Protected by Constraints

Entity integrity ensures that each row has a stable identity. Referential integrity ensures that relationships point to valid parent rows. Domain integrity keeps values within allowed ranges or states. Uniqueness prevents duplicate business keys, and mandatory-value rules reject missing required data.

Constraints do not replace all application logic. They are strongest for rules the database can evaluate consistently from the row, key, or relationship. Workflow decisions and rules depending on external services often belong elsewhere, supported by database invariants that prevent impossible states.

Major SQL Constraints

ConstraintPurposeExample rule
PRIMARY KEYIdentifies each row uniquelyEvery employee has one unique ID
UNIQUEPrevents duplicate key valuesNo two accounts share a normalized email
NOT NULLRequires a valueEmployee name cannot be missing
CHECKEnforces a conditionSalary must be nonnegative
FOREIGN KEYProtects parent-child relationshipsDepartment ID must exist
DEFAULTSupplies an omitted valueNew orders begin as pending

Identity and auto-increment features are often discussed with constraints but are value-generation mechanisms rather than relational constraints. Their exact behavior is database-specific.

Constraints during CREATE and ALTER

A rule can be defined when a table is created or added later with ALTER TABLE. Creation is simpler because an empty table has no historical violations.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

ALTER TABLE employees
ADD CONSTRAINT pk_employees
PRIMARY KEY (employee_id);

The second form must inspect current rows and may build an index, lock the table, consume disk, and fail on existing invalid data.

The Master Migration Rule

Before a constraint becomes fully valid, existing data must satisfy it. A new primary key cannot coexist with duplicate or NULL key values. A foreign key cannot be validated while orphan rows remain, and NOT NULL cannot be enforced while any row is missing the field.

NEW CONSTRAINT = VALID EXISTING DATA
               + ENFORCEMENT FOR FUTURE WRITES

A failure is useful evidence that actual data conflicts with the intended model. Investigate the records and business process rather than disabling the rule or deleting exceptions blindly.

Naming Constraints

Explicit names improve migration scripts, error diagnostics, documentation, and future removal. A consistent convention might use pk_, uq_, fk_, ck_, and df_ prefixes followed by table and column purpose.

Names must fit product length limits and schema scope. Keep them stable and meaningful rather than embedding temporary ticket numbers. Some inline definitions produce system-generated names; explicit naming is usually easier to manage when supported.

Adding a Primary Key

A primary key establishes row identity and requires unique, non-NULL values.

ALTER TABLE employees
ADD CONSTRAINT pk_employees
PRIMARY KEY (employee_id);

The operation normally creates or uses a unique index. Verify type suitability, stability, generation strategy, and every foreign-key relationship. A business value that changes frequently or contains sensitive information may be a poor primary key even when it is currently unique.

Checking Primary-Key Duplicates

Find duplicate candidates before attempting the constraint:

SELECT employee_id, COUNT(*) AS row_count
FROM employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

An empty result is expected. If duplicates exist, determine whether they represent repeated imports, distinct real entities sharing an incorrect identifier, or data that should be merged. Do not delete an arbitrary row without reconciling dependent data.

Checking Primary-Key NULLs

SELECT COUNT(*) AS null_key_count
FROM employees
WHERE employee_id IS NULL;

Assign identifiers through the approved generation strategy, not arbitrary constants. Validate that generated values do not collide and that applications, sequences, and integrations understand the final key.

Composite Primary Keys

A composite primary key uses multiple columns when their combination identifies a row.

ALTER TABLE enrollment
ADD CONSTRAINT pk_enrollment
PRIMARY KEY (student_id, course_id);

Each component becomes non-NULL under the primary key, and the pair must be unique. Check duplicate combinations with GROUP BY over every component. Column order can affect index access paths, so select it with query patterns and foreign keys in mind.

Primary Key Migration Risks

Adding a key to a large table can scan data, sort values, create an index, generate log or WAL, and block writes. If other tables already reference the intended key informally, adding formal foreign keys may become a separate migration.

Test index build duration and disk requirements. Where supported, create or attach a validated unique index using online options, then add the primary-key metadata. Product capabilities and limitations differ.

Adding a UNIQUE Constraint

A unique constraint protects a candidate key other than the primary key.

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email
UNIQUE (email);

Uniqueness should reflect business semantics. Raw emails may differ by case or whitespace while representing the same account under the application's rules. Normalize data and choose collation or expression-based indexing deliberately before enforcement.

Finding Duplicate Unique Values

SELECT email, COUNT(*) AS duplicate_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

Resolve duplicates through an approved merge or correction process. Review dependent orders, identities, permissions, and audit history before combining customer records. Making a constraint pass is not more important than preserving business truth.

UNIQUE and NULL

NULL behavior differs among database products and index forms. Some unique constraints permit multiple NULLs because unknown values are not considered equal; other systems or filtered indexes behave differently.

Do not rely on a memorized rule. Test the target DBMS and define whether the field is optional. If every row requires a unique value, combine UNIQUE with NOT NULL.

Composite UNIQUE Constraints

A composite unique rule prevents duplicate combinations while allowing individual values to repeat.

ALTER TABLE employees
ADD CONSTRAINT uq_employee_department_code
UNIQUE (department_id, employee_code);

The same employee code can appear in different departments, but not twice in one department. Confirm that this matches the business scope and test NULL behavior for every component.

Adding NOT NULL

NOT NULL is commonly added by altering the column rather than using ADD CONSTRAINT. Syntax varies significantly.

-- PostgreSQL
ALTER TABLE employees
ALTER COLUMN employee_name SET NOT NULL;

-- SQL Server-style definition
ALTER TABLE employees
ALTER COLUMN employee_name VARCHAR(100) NOT NULL;

MySQL commonly uses MODIFY COLUMN with the full definition, while Oracle uses MODIFY. Preserve type, length, default, collation, and other attributes required by the product.

Preparing for NOT NULL

SELECT COUNT(*) AS missing_names
FROM employees
WHERE employee_name IS NULL;

If missing values exist, recover them from an authoritative source, derive them under approved rules, or keep the field optional. Replacing every NULL with 'Unknown' may satisfy the database while creating meaningless data and duplicate business values.

Safe NOT NULL Rollout

First deploy writers that always provide the field. Backfill historical rows in bounded batches, monitor for new NULLs, validate the complete table, and only then enforce NOT NULL. Some products support validated check-constraint techniques that reduce the blocking validation work needed for the final metadata change.

Continue monitoring after enforcement because rejected writes can reveal forgotten consumers. The application should handle constraint errors clearly rather than converting them into generic server failures.

Adding a CHECK Constraint

A check constraint expresses a row-level condition.

ALTER TABLE employees
ADD CONSTRAINT ck_employees_salary
CHECK (salary >= 0);

Checks are useful for ranges, allowed states, relationships between columns in one row, and simple invariants. They protect every writer without requiring duplicated application validation.

CHECK and NULL

In SQL's three-valued logic, an expression involving NULL can evaluate to unknown rather than false, and a check generally rejects only false. Therefore CHECK (salary >= 0) may still permit NULL.

If salary must exist and be nonnegative, use both NOT NULL and CHECK. Each rule has a distinct responsibility and produces clearer intent.

Range and Allowed-Value Checks

ALTER TABLE products
ADD CONSTRAINT ck_products_discount
CHECK (discount_percent BETWEEN 0 AND 100);

ALTER TABLE orders
ADD CONSTRAINT ck_orders_status
CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED'));

Allowed-value checks are effective for small stable sets. If values carry attributes, ownership, or frequent changes, a reference table plus foreign key may model the domain better.

Cross-Column Checks

A check can compare fields in the same row:

ALTER TABLE bookings
ADD CONSTRAINT ck_bookings_date_order
CHECK (end_date >= start_date);

Rules involving other rows, aggregate totals, external services, or complex workflow state are usually poor check candidates. Use the right enforcement layer and keep the database invariant understandable.

Validating CHECK Data

SELECT employee_id, salary
FROM employees
WHERE salary < 0;

Investigate every violation. Negative values might be data-entry errors, reversal records, or a sign that the proposed rule is incomplete. Confirm semantics before correcting or rejecting them.

Adding a Foreign Key

A foreign key requires child values to match a candidate key in a parent table, unless the child is NULL and the relationship is optional.

ALTER TABLE employees
ADD CONSTRAINT fk_employees_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

The parent column must be primary or otherwise uniquely constrained according to the DBMS. Child and parent types, sizes, signedness, collations, and composite-key order must be compatible.

Finding Orphan Rows

SELECT e.employee_id, e.department_id
FROM employees AS e
LEFT JOIN departments AS d
  ON d.department_id = e.department_id
WHERE e.department_id IS NOT NULL
  AND d.department_id IS NULL;

Resolve orphans by correcting the child key, creating a legitimate missing parent, setting an optional relationship to NULL, removing genuinely invalid child data, or redesigning the relationship. Creating a fake parent only to satisfy the constraint hides the underlying problem.

Composite Foreign Keys

A composite child key must reference the parent key using compatible columns and order.

ALTER TABLE employees
ADD CONSTRAINT fk_employee_department_region
FOREIGN KEY (department_id, region_id)
REFERENCES departments(department_id, region_id);

Profile partial NULL combinations and understand the DBMS's matching semantics. Index child columns when joins and parent updates or deletes justify it; many systems do not create that index automatically.

Referential Actions

Foreign keys may define ON DELETE and ON UPDATE actions such as CASCADE, SET NULL, RESTRICT, NO ACTION, or product-specific SET DEFAULT.

These are business decisions. CASCADE can be correct for dependent line items owned entirely by an order, but dangerous for independently valuable records. SET NULL requires nullable child fields and a meaningful optional relationship. Test multi-level cascades and transaction volume before production.

Foreign-Key Indexing

The parent candidate key is indexed by its uniqueness rule, but the child foreign-key columns may not be. An appropriate child index can improve joins and reduce scans during parent delete or update checks.

Do not create indexes mechanically. Consider column order, selectivity, existing composite indexes, write overhead, and real query plans. Constraint correctness and query performance are related but separate design decisions.

Adding a DEFAULT

A default supplies a value when an INSERT omits the column. It does not reject explicit NULL unless NOT NULL also applies, and changing a default normally does not rewrite historical rows.

-- PostgreSQL example
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'PENDING';

SQL Server represents defaults as separately named constraint objects, while MySQL and Oracle use their own ALTER syntax. Choose a default that represents real initial state rather than masking missing input.

DEFAULT versus Existing Data

If old rows need the new default value, backfill them explicitly after confirming the meaning. A field may contain NULL because the value is unknown, which is different from a new order being pending.

Separate future insert behavior from historical correction. Validate row counts and application behavior for both populations.

Constraint Selection

RequirementLikely ruleAdditional consideration
Every row needs an IDPRIMARY KEYStable generation and foreign keys
Email cannot repeatUNIQUENormalization, collation, NULL
Name is mandatoryNOT NULLBackfill and all writers
Price cannot be negativeCHECKNULL requires separate handling
Department must existFOREIGN KEYOrphans and referential actions
Omitted status starts pendingDEFAULTDoes not enforce NOT NULL

Several constraints may work together. Avoid overloading one rule with responsibilities it does not provide.

Cross-Database Differences

MySQL, PostgreSQL, SQL Server, Oracle, and SQLite differ in ALTER syntax, constraint naming, NULL uniqueness, check enforcement history, deferred constraints, validation options, transaction behavior, and online operations. SQLite may require a table rebuild for some changes.

Test on the exact database version and configuration. Documentation for a newer release or another product is not sufficient migration evidence.

Staged Constraint Validation

Some products can create a constraint that applies to new changes without immediately validating every historical row, then validate old data in a later step. PostgreSQL offers NOT VALID for certain constraints; SQL Server has WITH NOCHECK and later trust semantics; Oracle provides enable and validate modes.

These mechanisms can reduce deployment blocking, but an unvalidated or untrusted constraint is not equivalent to a fully validated one. Track the state explicitly and complete validation under an approved plan.

Large Tables and Locking

Adding a constraint can scan the complete table, sort keys, build an index, lock writes, generate log or WAL, consume temporary disk, and delay replicas. A rule that validates instantly on a development table may take hours in production.

Benchmark production-like volume and distribution. Understand lock acquisition and hold time, online index support, timeouts, storage headroom, replication lag, and cancellation behavior. Define abort thresholds and responder ownership before execution.

Cleaning Data Safely

Data cleanup should follow documented business rules and preserve an audit trail. Use bounded, restartable batches for large updates, record rejected records, and reconcile aggregates after changes.

UPDATE employees
SET department_id = source.department_id
FROM trusted_employee_source AS source
WHERE employees.employee_id = source.employee_id
  AND employees.department_id IS NULL;

Do not invent placeholder identifiers or delete violations merely to make validation pass. Constraint failures often reveal process defects that also need correction.

Application Compatibility

A new constraint can reject writes that previously succeeded. Every service, import, job, support tool, and integration must supply valid values and handle database errors appropriately. Older application instances are especially important during rolling deployments.

Deploy validation and corrected writers before enforcement. Align API schemas, ORM annotations, client-side rules, event contracts, ETL mappings, and test fixtures, while keeping the database as the final authority.

Deployment Sequence

  1. Define the business invariant and owning team.
  2. Profile current data and identify every violation.
  3. Update all writers and user-facing validation.
  4. Clean or migrate historical rows using approved rules.
  5. Create supporting indexes or staged validation where useful.
  6. Add and fully validate the constraint.
  7. Run application and operational tests, then monitor rejections.

Every intermediate state must be compatible with applications active during deployment. Separate risky cleanup, index creation, and final enforcement when that improves control.

Rollback and Forward Repair

Dropping a newly added constraint can restore permissive writes, but it does not reverse data cleanup or application changes. If the rule rejects valid business operations, first determine whether the constraint definition or the incoming data is wrong.

Prepare a rollback statement, preserve original values during destructive cleanup, and coordinate database and application rollback. Forward repair may be safer when valid data has already been written under the new model.

Transactions and DDL

DDL transaction behavior differs. PostgreSQL supports many transactional schema changes, while MySQL and Oracle commonly commit implicitly. SQL Server and SQLite have their own rules and restrictions.

Test whether cancellation or rollback removes partially created indexes and constraints. Do not assume one product's behavior applies to another.

Constraint Errors and Observability

After enforcement, monitor constraint-violation errors by constraint name, application version, endpoint, job, and source system. Avoid logging sensitive values unnecessarily. A spike can reveal a forgotten writer or incorrect assumption.

Expose clear user-facing errors for expected validation failures while preserving detailed diagnostic context for operators. Constraint names should make alerts understandable.

Partitioned, Sharded, and Replicated Data

In distributed schemas, the constraint may need to exist on every partition or shard. Some cross-shard relationships cannot be enforced by a local foreign key, and replicas may lag during index creation or validation.

Inventory every physical table and migration version. Ensure rolling changes tolerate mixed states and compare catalogs across the fleet before declaring success. Test failover while the new constraint exists.

Security and Governance

Constraints can support security by rejecting impossible states, but they are not authorization controls. A unique token, mandatory owner, or allowed status still needs access control, encryption, auditing, and privacy handling.

Document the rule in the data catalog, including business owner, definition, exceptions, enforcement date, cleanup decision, and validation evidence. Governance helps future teams understand why a constraint exists before attempting to remove it.

Validation after Adding Constraints

Inspect catalog metadata to confirm the expected name, columns, expression, reference, actions, and validation state. Repeat duplicate, NULL, orphan, and range queries and expect zero violations.

Test valid and invalid inserts and updates, parent deletes, bulk imports, APIs, reports, ETL, and background jobs. Verify index usage and critical query plans. Monitor locks, latency, log growth, replica health, and rejected writes through a meaningful period.

Deferrable Constraints

Some databases support deferrable primary, unique, or foreign-key constraints. A normal immediate constraint is checked at the end of each statement, while a deferred constraint can be checked at transaction commit. Deferral can help when a valid multi-step transaction temporarily passes through an intermediate state that would otherwise violate the rule.

Deferrable behavior is product-specific and should not become a general way to postpone integrity. The final transaction must still be valid, and longer invalid intervals can make debugging harder. Document which transactions need deferral, set the intended initial mode explicitly, and test commit-time errors. Applications must handle a failure that appears at commit rather than at the original statement.

Concurrent Writes during Cleanup

A cleanup can reach zero violations and still lose the race if an old application inserts another invalid row before the constraint is enforced. This is common during long backfills or phased deployments. The migration needs a strategy for historical rows and for traffic arriving during the transition.

Deploy corrected writers first, use staged constraint validation when supported, or place a temporary enforcement mechanism at the database boundary. Re-run validation immediately before the final ALTER statement and monitor the violating query continuously. If the system cannot prevent new bad rows, the migration is not ready for final enforcement regardless of how thoroughly the old data was cleaned.

Handling Legitimate Exceptions

A proposed rule may have rare valid exceptions. For example, most employees have a department, but contractors awaiting assignment may legitimately have none. Forcing a fake department produces technically valid but misleading data. Keeping the relationship nullable and recording an assignment status may model reality better.

Classify exceptions before designing the constraint. Determine whether they represent a separate entity subtype, temporary workflow state, optional relationship, historical import, or genuine data defect. Model valid exceptions explicitly rather than weakening the rule for every row. When temporary exceptions require an operational waiver, record the owner, reason, expiry, and path to full compliance.

Migration Preflight Gates

A production preflight should verify the exact server, database, schema, table, current definition, expected row count range, and migration checksum. It should rerun violation queries, confirm compatible application versions are deployed, check available disk, inspect blockers, and ensure the requested constraint name is unused.

Bind approval to those verified facts. If the schema, data profile, target, or migration artifact changes, require renewed review. Automated gates reduce wrong-environment execution and stale assumptions, but they do not replace business-owner approval for cleanup and referential actions. Store preflight output with the migration record so later investigation can reconstruct the decision.

Constraint Contract Testing

Tests should prove both sides of the contract. Valid boundary values must succeed, and invalid values must fail with the intended constraint. For a range check, test minimum, maximum, values just outside each boundary, NULL behavior, and numeric precision. For a foreign key, test valid parent references, missing parents, NULL children, and each delete or update action.

Run these tests through actual application paths as well as direct SQL. Verify transaction rollback, bulk loaders, retries, API error mapping, and ORM behavior. Keep the tests in the automated suite so a future migration cannot silently weaken or bypass the invariant. Database catalog assertions can also confirm that the constraint remains enabled and trusted.

Evolving Constraints Later

Business rules change, so a constraint may eventually need replacement. Directly dropping the old rule before adding the new one creates a window where invalid data can enter. When possible, introduce the broader or alternative rule, migrate applications and data, validate it, and remove the obsolete rule only after the new contract is active.

Some changes cannot coexist because their definitions conflict. In that case, use a controlled transaction where supported or a short maintenance window, and prevent unvalidated writes during the gap. Preserve constraint definitions in version control and explain why the rule changed. Future engineers need the business history, not only the latest DDL.

Failure Response after Enforcement

If write failures increase after deployment, identify the exact named constraint, source application version, statement path, and input category. Do not drop the rule immediately without deciding whether valid business data is being rejected or a forgotten writer is sending invalid data. Preserve diagnostics while protecting sensitive values.

A valid constraint usually calls for forward repair of the writer or source data. An incorrect constraint may need rollback or replacement, coordinated with application behavior. After recovery, update tests, dependency inventories, and rollout gates to include the missed case. Constraint incidents should improve the model and migration process rather than merely restore permissive behavior.

Common Mistakes

  • Adding a constraint before profiling historical data.
  • Deleting duplicate or orphan rows without understanding ownership.
  • Using fake placeholder values to satisfy NOT NULL or foreign keys.
  • Assuming UNIQUE has identical NULL behavior on every DBMS.
  • Believing CHECK automatically rejects NULL.
  • Using CASCADE without confirming the ownership lifecycle.
  • Forgetting child indexes and query-performance effects.
  • Ignoring old application versions, imports, reports, and ETL.
  • Leaving a constraint unvalidated or untrusted indefinitely.
  • Assuming constraint creation is instant and requires no lock.
  • Relying on application validation instead of database enforcement.
  • Using unnamed, unstable constraints that are difficult to operate.

Real-World Constraint Migration

A customer platform wants every account to have one normalized email and a valid organization. Profiling finds duplicate emails caused by case differences, NULL emails for imported records, and organization IDs that no longer exist. Adding UNIQUE, NOT NULL, and FOREIGN KEY immediately would fail and could block valid operations.

The team defines normalization and account-merge rules with business owners. It updates every writer to store normalized email, repairs or quarantines invalid imports, reconciles duplicate accounts and dependent orders, and corrects organization mappings from an authoritative source. Batched cleanup is audited and repeatable.

It creates the supporting unique index using the safest available online method, validates the relationship in stages, enforces NOT NULL after all writers are upgraded, and monitors named constraint errors. The final constraints capture rules that the complete system already follows rather than attempting to create correctness through DDL alone.

Interview Questions and Answers

Why add constraints? To make the database reject invalid states consistently for every writer.

What must be true before adding a constraint? Existing data must satisfy the rule or use a supported staged-validation plan.

What does a primary key enforce? Unique, non-NULL row identity.

How is UNIQUE different from PRIMARY KEY? A table has one primary key but can have several candidate keys protected by UNIQUE; NULL behavior is product-specific.

Does CHECK reject NULL automatically? Not necessarily, because the expression can evaluate to unknown. Use NOT NULL separately when required.

What is an orphan row? A child row whose non-NULL foreign-key value has no matching parent.

Should ON DELETE CASCADE always be used? No. It is appropriate only when deleting the parent should legitimately delete the dependent children.

Does DEFAULT make a field mandatory? No. It supplies omitted values; NOT NULL rejects NULL.

Why name constraints? Stable names improve errors, migrations, monitoring, documentation, and future removal.

Can constraint creation block production? Yes. Validation and index building can scan, lock, log, and consume substantial resources.

Production Checklist

  • Define the exact invariant, owner, scope, and exception policy.
  • Verify server, database, schema, table, columns, and current definitions.
  • Profile duplicates, NULLs, invalid ranges, and orphan relationships.
  • Resolve violations through approved business rules and preserve evidence.
  • Update applications, APIs, ORMs, imports, ETL, and background jobs.
  • Choose stable names, indexes, reference actions, and DBMS-specific syntax.
  • Test locking, validation time, storage, logging, replicas, and cancellation.
  • Plan staged enforcement, monitoring, rollback, and forward repair.
  • Validate catalog state, data, valid and invalid writes, and query plans.
  • Update documentation, lineage, runbooks, and ownership records.

Key Takeaway

Adding constraints converts business invariants into database-enforced rules. The statement is only the final step: integrity begins by defining the correct rule, proving that historical data follows it, and updating every writer to remain compliant.

SAFE CONSTRAINT MIGRATION = CORRECT BUSINESS RULE
                          + PROFILED EXISTING DATA
                          + APPROVED CLEANUP
                          + COMPATIBLE WRITERS
                          + DBMS-SPECIFIC ENFORCEMENT
                          + VALIDATION
                          + MONITORING

Use primary keys for identity, unique rules for candidate keys, NOT NULL for mandatory values, checks for row-level domains, foreign keys for relationships, and defaults for omitted initial values. Add each rule only after the data and the complete application ecosystem are ready to honor it.