Constraints in SQL and Databases
Introduction
A database constraint is a rule that controls which values and relationships are allowed in a table. Constraints turn important assumptions into conditions that the database checks whenever data is inserted or updated, and in some cases when referenced rows are deleted. If a proposed change violates a declared rule, the DBMS rejects the operation instead of permanently storing an invalid state.
Constraints protect against duplicate identifiers, missing mandatory values, negative prices, unsupported status codes, broken table relationships, and many other data-quality problems. They remain active regardless of whether a write comes from a web application, API, batch import, administrative tool, scheduled job, or direct SQL script.
The six core concepts usually taught together are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. Some database products classify DEFAULT as a column property rather than a constraint object, but it is commonly discussed with constraints because it governs how omitted values are populated. This tutorial explains their purposes, syntax, interactions, migrations, performance considerations, testing, and practical design choices.
Why Constraints Are Important
Application forms can validate user input, but applications are not the only writers to a database. Even one application may have several code paths, old releases, asynchronous workers, and concurrent requests. Repeating every rule perfectly in every writer is difficult, and a preliminary validation check can become stale before a transaction commits.
Constraints place the final safety boundary next to the data. They make invalid states unrepresentable for every compliant write. A duplicate email cannot slip through a race when a Unique constraint exists. An order cannot reference a nonexistent customer when a Foreign Key is enforced. A negative quantity cannot be stored when a suitable CHECK condition rejects it.
Constraints also document intent for developers, schema tools, migration systems, and query optimizers. A table definition becomes a compact description of row identity, mandatory attributes, accepted domains, and relationships rather than a loose collection of columns.
The Core Constraint Types
| Constraint or rule | Primary purpose | Typical question answered |
|---|---|---|
| PRIMARY KEY | Unique row identity | Which row is this? |
| FOREIGN KEY | Valid relationship | Does the referenced row exist? |
| UNIQUE | Prevent duplicate values or tuples | May this value repeat? |
| NOT NULL | Require a value | May this attribute be absent? |
| CHECK | Require a Boolean condition | Does this row satisfy its domain rule? |
| DEFAULT | Supply a value when omitted | What should be inserted automatically? |
These rules are complementary. A column can be required, unique, checked against a condition, and given a default. Relationships combine Foreign Keys with nullability and sometimes uniqueness to express cardinality. Good schemas use several focused constraints rather than expecting one rule to cover unrelated responsibilities.
PRIMARY KEY Constraint
A Primary Key is the selected principal identity for rows in a table. Its complete value must be unique, and no Primary Key column may be NULL. A table has one Primary Key constraint, although that constraint can contain one column or several columns.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
Two customers cannot have the same customer_id. The database also rejects an attempt to omit or explicitly assign NULL to that key. In many application schemas, the Primary Key is a generated surrogate ID. In other tables, a stable natural value or composite business key may be appropriate.
Primary Key generation is separate from the constraint. Identity, auto-increment, sequences, and UUID generators produce values; PRIMARY KEY enforces identity. An automatically generated column is not conceptually a Primary Key until the schema declares it as one.
Composite Primary Keys
A composite Primary Key uses several columns as one identity. This is common in pure relationship tables where the pair itself defines the row.
CREATE TABLE student_courses (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL,
CONSTRAINT pk_student_courses
PRIMARY KEY (student_id, course_id)
);
A student can enroll in several courses, and a course can contain several students, but the same student-course pair cannot appear twice. Neither component may be NULL because both participate in the Primary Key.
Composite keys accurately express many natural identities, but every referencing Foreign Key must normally carry the same columns. When that becomes cumbersome, a surrogate Primary Key plus a composite Unique constraint can preserve business uniqueness while simplifying references.
FOREIGN KEY Constraint
A Foreign Key protects a relationship by requiring each asserted child value to match an existing Primary Key or eligible Unique Key in the parent table. If the child columns permit NULL, NULL can represent an optional relationship.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_total DECIMAL(12, 2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);
An order for customer 101 succeeds only when customer 101 exists. Customer 999 is rejected if no matching parent row exists. The Foreign Key does not make customer_id unique in orders; many orders may reference one customer.
Delete and update actions such as RESTRICT, CASCADE, and SET NULL define what happens when a referenced parent changes. Those actions should reflect lifecycle ownership and retention requirements.
UNIQUE Constraint
A Unique constraint prevents duplicate values in one column or duplicate combinations across several columns. It protects natural candidate keys and additional business rules while the table may use a different Primary Key.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_number VARCHAR(30) NOT NULL,
email VARCHAR(150) NOT NULL,
CONSTRAINT uq_employee_number UNIQUE (employee_number),
CONSTRAINT uq_employee_email UNIQUE (email)
);
A table can have several Unique constraints. Unlike a Primary Key, the treatment of NULL in a Unique constraint differs across database products and definitions. If the value is a mandatory candidate identity, combine NOT NULL and UNIQUE explicitly.
Uniqueness also depends on collation and normalization. Case, whitespace, and Unicode rules must match the business meaning, especially for email, usernames, and external codes.
Composite UNIQUE Constraints
A composite Unique constraint rejects a repeated complete tuple while allowing individual columns to repeat. This is how databases express scoped uniqueness.
CREATE TABLE enrollments (
enrollment_id BIGINT PRIMARY KEY,
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
CONSTRAINT uq_enrollment_student_course
UNIQUE (student_id, course_id)
);
Student 101 can appear in many courses, and course 10 can contain many students. Only the pair (101, 10) is restricted from repeating. Other examples include UNIQUE (tenant_id, username), UNIQUE (company_id, employee_number), and UNIQUE (warehouse_id, product_id, bin_code).
Column order does not change which exact tuples are duplicates, but it can affect how the supporting index serves queries. Integrity should be modeled first, then index order should be reviewed against measured access patterns.
NOT NULL Constraint
NOT NULL requires a column to contain a known, non-NULL value in every row. It expresses mandatory participation or mandatory attributes.
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
unit_price DECIMAL(12, 2) NOT NULL
);
NOT NULL does not prevent empty strings, zeros, placeholder dates, or meaningless text such as 'UNKNOWN'. Those are values, not SQL NULL. If they are invalid, use additional CHECK conditions, reference tables, normalization rules, or business logic.
Nullability also controls relationships. A Foreign Key plus NOT NULL means every child must identify a parent. A nullable Foreign Key means the relationship may be absent, although every non-NULL value must still reference a valid parent.
CHECK Constraint
A CHECK constraint requires a condition to evaluate as satisfied for a row. It is appropriate for domain rules involving values in the same row, such as nonnegative quantities, valid percentages, date ordering, or an allowed set of status codes.
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
unit_price DECIMAL(12, 2) NOT NULL,
discount_percent DECIMAL(5, 2),
CONSTRAINT chk_product_price
CHECK (unit_price >= 0),
CONSTRAINT chk_product_discount
CHECK (discount_percent BETWEEN 0 AND 100)
);
SQL uses three-valued logic. In many systems, a CHECK rejects FALSE but does not reject UNKNOWN. If discount_percent is NULL, the comparison may be UNKNOWN and the row can satisfy the constraint. Add NOT NULL when a value is mandatory, or write the NULL policy directly into the condition.
CHECK Constraints with Multiple Columns
Table-level CHECK constraints can compare several columns in the same row. This is useful for date ranges and conditional requirements.
CREATE TABLE promotions (
promotion_id BIGINT PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status VARCHAR(20) NOT NULL,
approved_at TIMESTAMP NULL,
CONSTRAINT chk_promotion_dates
CHECK (end_date >= start_date),
CONSTRAINT chk_promotion_approval
CHECK (status <> 'APPROVED' OR approved_at IS NOT NULL)
);
CHECK is not suited to every cross-row or cross-table rule. Preventing overlapping bookings, limiting the sum of line items, or confirming a parent status often requires specialized database features or transaction-safe application and database logic. Product support for functions and subqueries inside CHECK expressions varies.
Status CHECK Versus Lookup Table
A CHECK list is convenient for a small, stable set of values.
CONSTRAINT chk_order_status
CHECK (status IN (
'NEW', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED'
))
A lookup table is often better when statuses have labels, display order, translations, metadata, active periods, or administrative management. A Foreign Key can then ensure that the selected status exists.
The choice depends on ownership and change frequency. A CHECK makes the allowed set part of the table schema and requires a migration to change. A lookup table makes values data, but it also introduces lifecycle questions such as whether a referenced status can be deleted. Neither approach automatically enforces valid transitions from one status to another; workflow rules may need separate logic.
DEFAULT Values
A DEFAULT supplies a value when an insert omits the column. It improves consistency and reduces repetitive insert syntax.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DEFAULT is not general validation. The default status does not prevent a caller from explicitly inserting 'INVALID'; a CHECK or Foreign Key is needed for the allowed domain. A default quantity of 1 does not prevent a negative explicit quantity; CHECK handles that rule.
Omitted and explicit NULL are also different. In many systems, a default is used when the column is omitted or the DEFAULT keyword is requested, while explicitly supplying NULL stores NULL or fails under NOT NULL. Verify exact DBMS behavior rather than assuming DEFAULT replaces every missing or NULL input.
Combining DEFAULT, NOT NULL, and CHECK
These three declarations answer different questions. DEFAULT chooses an automatic value when one is not supplied, NOT NULL requires that stored value to exist, and CHECK limits which stored values are valid.
CREATE TABLE order_items (
order_item_id BIGINT PRIMARY KEY,
quantity INT NOT NULL DEFAULT 1,
discount_percent DECIMAL(5, 2) NOT NULL DEFAULT 0,
CONSTRAINT chk_item_quantity CHECK (quantity > 0),
CONSTRAINT chk_item_discount
CHECK (discount_percent BETWEEN 0 AND 100)
);
The default helps ordinary inserts, but every explicit value still passes through the mandatory and range checks. Layering small constraints makes intent clearer than relying on one broad application validation function.
Column-Level and Table-Level Syntax
Simple constraints can appear beside a column definition. Table-level syntax is necessary for multi-column constraints and is often clearer when constraints are named.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
username VARCHAR(80) NOT NULL,
age INT CHECK (age >= 18),
CONSTRAINT uq_tenant_username
UNIQUE (tenant_id, username)
);
Whether a single-column rule is written at column or table level usually does not change its logical effect. Team conventions, migration tooling, and DBMS syntax determine the preferred style. Explicit table-level names are useful for operational work and error mapping.
Naming Constraints
Meaningful names make schema maintenance and failure diagnosis easier. An error naming uq_users_tenant_email communicates more than a generated identifier. Migration scripts can also target the exact rule reliably.
pk_orders
fk_orders_customer
uq_users_tenant_email
chk_orders_total_nonnegative
A common pattern combines a short type prefix, table name, and relevant columns or purpose. Names must remain within DBMS identifier limits and unique within the required schema scope. Consistency matters more than one universal convention.
Applications can map known constraint names to domain messages such as "Email already exists" without parsing vendor-specific error text. They should still handle unknown integrity failures safely.
Database Constraints Versus Application Validation
Application validation and database constraints serve different audiences. The application can provide immediate, contextual feedback, explain several problems at once, and enforce workflow rules that are difficult to express in DDL. The database protects the shared state from every writer and resolves concurrent races.
Use both layers. Validate early for usability, then let constraints remain authoritative. When a constraint rejects a write, catch the integrity exception, roll back the transaction as required, and translate the failure into a meaningful application response. Do not expose raw SQL or internal schema details to end users.
Duplicating a rule at two layers is not wasteful when each layer has a distinct purpose. It becomes dangerous only when their definitions drift, which is why tests should exercise the actual database as well as application validators.
Constraints and Data Integrity
Entity Integrity ensures that every row has a valid identity, usually through a Primary Key. Referential Integrity ensures that child references point to existing eligible parent rows, through Foreign Keys. Domain Integrity restricts attribute values through data types, nullability, CHECK conditions, and appropriate defaults.
Business Integrity covers rules specific to the application, such as one employee number per company or an end date not preceding a start date. Some business rules map directly to Unique or CHECK constraints. Others involve multiple rows, aggregates, time ranges, workflow history, or external facts and require transaction-safe logic or specialized database features.
Classifying a rule helps select the simplest reliable mechanism. Constraints are powerful precisely because each one expresses a focused invariant.
Multiple Constraints on One Column
A column can participate in several rules simultaneously. An email may be required and unique; a price may be required, defaulted, and checked; a mandatory parent ID may be both NOT NULL and a Foreign Key.
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
email VARCHAR(150) NOT NULL UNIQUE,
age INT NOT NULL CHECK (age >= 18),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
CONSTRAINT chk_users_status
CHECK (status IN ('ACTIVE', 'LOCKED', 'DISABLED'))
);
Each declaration contributes one part of the valid state. The database may report only one violation from a failing statement, and the order in which checks are reported should not be treated as a portable contract.
Constraint Enforcement on INSERT and UPDATE
An INSERT must satisfy every relevant rule: required values, key uniqueness, row checks, and references. An UPDATE revalidates constraints affected by the changed values. Changing an email to one already used fails its Unique constraint; changing a customer ID to a nonexistent value fails its Foreign Key; reducing quantity below zero fails CHECK.
Constraints also interact with multi-row statements. The DBMS evaluates the final changes according to its statement and transaction semantics. Deferrable constraints, when supported, may postpone selected checks until commit. Applications should not depend on a particular violation being reported first when several values are invalid.
Adding Constraints After Table Creation
Constraints can be introduced with ALTER TABLE, using syntax appropriate to the database product.
ALTER TABLE products
ADD CONSTRAINT chk_products_price
CHECK (unit_price >= 0);
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
Existing rows normally must satisfy the new rule. A Unique constraint fails when duplicates exist, a Foreign Key fails when orphans exist, and CHECK fails when old values violate its condition. Profile and clean the data before applying the migration.
Large tables may require staged validation or online capabilities to control locking and deployment time. Product features differ, so migration plans should include concurrency, rollback, replication, and final trusted validation.
Finding Invalid Existing Data
Duplicate values can be found by grouping the proposed key.
SELECT email, COUNT(*) AS duplicate_count
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
Orphaned relationships can be found with NOT EXISTS.
SELECT o.order_id, o.customer_id
FROM orders AS o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM customers AS c
WHERE c.customer_id = o.customer_id
);
CHECK violations should be queried using the inverse condition while accounting for NULL explicitly. Resolution is a business decision: merge duplicates, correct values, create legitimate parents, reassign records, or quarantine data. Deleting rows only to make DDL succeed can destroy valuable history.
Dropping or Changing Constraints
Removing a constraint changes what the database considers valid. It may affect application assumptions, Foreign Key dependencies, upsert targets, query optimization, and compliance controls. Treat the change as a schema and domain migration, not routine cleanup.
Drop syntax varies, especially for defaults, indexes used by uniqueness, and product-generated names. This is another reason to name constraints explicitly and use the project's migration framework.
Before weakening a rule, identify why it no longer matches the business. Often the correct solution is to change scope, such as replacing global username uniqueness with UNIQUE (tenant_id, username), rather than removing protection entirely.
Constraints and Relationships
Relationships commonly require several constraints together. A mandatory one-to-many child uses NOT NULL plus a Foreign Key. A one-to-one child adds UNIQUE to the Foreign Key. A many-to-many junction uses two Foreign Keys plus a composite Primary Key or Unique Key.
CREATE TABLE employee_badges (
badge_id BIGINT PRIMARY KEY,
employee_id BIGINT NOT NULL UNIQUE,
issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);
The Foreign Key proves the employee exists, NOT NULL makes the relationship mandatory for every badge, and UNIQUE permits at most one badge row per employee. Cardinality comes from the combination rather than the Foreign Key alone.
Constraints and Transactions
When a constraint fails, the statement is rejected, and transaction behavior then follows the DBMS and error-handling context. Applications should roll back the appropriate unit of work and avoid leaving related changes partially committed.
Transactions also let operations maintain constraints through ordered steps. A restrictive parent deletion can first delete or reassign child rows, then delete the parent before commit. Systems supporting deferred constraints may allow temporary inconsistency within a transaction and validate at commit, but this must be configured deliberately.
Constraints do not replace transaction design. They define valid states; transactions coordinate several changes so the database moves atomically from one valid state to another.
Bulk Loading and Constraint Validation
Bulk operations may expose constraint costs because every row must be checked and supporting indexes maintained. Use batch sizes, parent-first load order, staging tables, and vendor-supported bulk methods. Do not disable constraints casually to obtain faster loads.
If constraints are temporarily disabled, the process must prevent concurrent invalid writes, validate all loaded data, and restore the constraints to a trusted state. A failure between disable and validation can leave silent corruption. Staging data outside the protected production tables is often safer because transformation and quality checks happen before the final constrained insert.
Constraints, Indexes, and Performance
Primary Key and Unique constraints are commonly supported by unique indexes or equivalent structures. These structures can improve equality lookups and joins while adding storage and write-maintenance cost. Constraint intent and physical indexing remain conceptually distinct even when one object implements both.
Referenced parent keys are uniquely indexed. Child Foreign Key columns are not automatically indexed in every DBMS, but an appropriate child index can improve joins and the work needed to check parent updates or deletes.
CHECK and NOT NULL evaluations are usually inexpensive compared with the cost of repairing corrupted data. Performance should be measured with realistic workloads. Removing valid constraints is rarely the first or best optimization; redundant indexes, oversized key types, poor batching, and unselective access patterns should be investigated first.
Constraints and Normalization
Normalization organizes facts into appropriate tables and reduces update anomalies. Constraints preserve the assumptions that make the normalized design reliable. Primary Keys identify entities, Unique constraints protect candidate identities, and Foreign Keys reconnect related facts without allowing invalid references.
Constraints cannot rescue a poorly chosen row grain. If one table mixes several entities or stores repeated lists in text, individual CHECK conditions will not provide a sound relational model. Start with entity and relationship design, then use constraints to encode its invariants.
Mapping Business Rules to Constraints
Translate each precise business statement into the narrowest reliable database mechanism. "Every order has a customer" maps to customer_id NOT NULL plus a Foreign Key. "An employee number is unique inside a company" maps to a composite Unique constraint. "Quantity must be positive" maps to CHECK.
Some rules cannot be represented by basic constraints alone. "A room cannot have overlapping reservations" compares multiple time ranges. "Credit exposure must remain below a limit" may aggregate several rows and external facts. These need transaction-safe algorithms, specialized constraints where supported, or controlled service logic. The database should still enforce all simpler component rules it can express.
Complete Employee Example
CREATE TABLE departments (
department_id BIGINT PRIMARY KEY,
department_code VARCHAR(20) NOT NULL UNIQUE,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
company_id BIGINT NOT NULL,
employee_number VARCHAR(30) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
department_id BIGINT NOT NULL,
salary DECIMAL(12, 2) NOT NULL,
employment_status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
CONSTRAINT uq_company_employee_number
UNIQUE (company_id, employee_number),
CONSTRAINT chk_employee_salary
CHECK (salary >= 0),
CONSTRAINT chk_employment_status
CHECK (employment_status IN
('ACTIVE', 'LEAVE', 'TERMINATED')),
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments (department_id)
);
The Primary Key identifies each employee. Composite uniqueness protects company-scoped employee numbers. Email cannot duplicate. Required fields reject NULL. CHECK conditions protect salary and status domains. The default supplies an initial status, and the Foreign Key guarantees a valid department. No single constraint replaces another; together they define the valid row.
Common Constraint Mistakes
Relying only on application code leaves alternate writers and races unprotected. Using too few constraints allows known invalid states, while adding constraints without a real requirement rejects valid data. A Unique constraint on a one-to-many Foreign Key accidentally limits the relationship to one-to-one.
Other mistakes include treating DEFAULT as validation, forgetting NOT NULL because a default exists, using CHECK limits that do not match boundary requirements, overlooking composite business uniqueness, and enabling cascade deletion without lifecycle analysis. Ignoring names makes errors and migrations harder to manage.
Finally, SQL syntax and behavior are not identical across products. NULL uniqueness, CHECK expression capabilities, deferrability, default handling, referential actions, online validation, and drop syntax must be tested in the actual DBMS.
Constraint Testing Strategy
For every rule, test at least one valid boundary and each important invalid boundary. Primary and Unique keys need duplicate insert and duplicate update cases. NOT NULL needs omitted and explicit NULL cases. CHECK needs values below, at, and above limits, plus NULL behavior. Foreign Keys need valid, invalid, optional, mandatory, update, and delete cases.
Test defaults by omitting the column and by explicitly supplying another valid value, an invalid value, and NULL. Test composite constraints with individual components repeating while the complete tuple duplicates. Run concurrent requests against uniqueness rules to prove the database resolves races.
Migration tests should start with representative legacy data, reveal duplicates and orphans, and confirm the final constraint is enabled and trusted. Application tests should verify that integrity exceptions become clear domain responses.
Constraint Design Workflow
Begin with the data rule in business language. Decide whether it concerns identity, relationships, uniqueness, mandatory values, a row condition, or an omitted-value default. Select the narrowest appropriate mechanism and define its scope, NULL behavior, and lifecycle.
Define the table grain and business rule
-> classify identity, relationship, domain, or default behavior
-> select PRIMARY KEY, FOREIGN KEY, UNIQUE,
NOT NULL, CHECK, or DEFAULT
-> choose column-level or table-level syntax
-> name the constraint consistently
-> inspect and clean existing data
-> implement through a controlled migration
-> test valid, invalid, boundary, NULL, and concurrent cases
-> review DBMS-specific behavior and operational cost
Document why each constraint exists. Future maintainers can then adapt the rule when business requirements change rather than bypassing it blindly.
Complete Order Example
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
order_number VARCHAR(30) NOT NULL UNIQUE,
customer_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
total_amount DECIMAL(12, 2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id),
CONSTRAINT chk_orders_status
CHECK (status IN
('NEW', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED')),
CONSTRAINT chk_orders_total
CHECK (total_amount >= 0)
);
The order ID supplies row identity, order number provides business uniqueness, customer is required and must exist, status receives a valid initial value, total cannot be negative, and creation time is populated when omitted. The table remains protected even when a writer bypasses the usual application form.
Interview Questions and Answers
What is a database constraint? A schema rule that limits accepted values or relationships to protect data integrity.
What are the major SQL constraints? PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK, with DEFAULT commonly taught alongside them.
Can one column have several constraints? Yes. A column can be NOT NULL, UNIQUE, checked, defaulted, and participate in a key where the rules are compatible.
What is the difference between PRIMARY KEY and UNIQUE? The Primary Key is the selected principal row identity and cannot contain NULL. A table can have several additional Unique constraints, whose NULL behavior depends on the DBMS.
Does DEFAULT validate values? No. It supplies a value when appropriate but does not prevent other explicit values.
Why use database constraints when the application validates input? Constraints protect every writer and safely resolve concurrent changes at the shared data boundary.
Can constraints be added later? Yes, but existing data must be cleaned or validated and production migration effects must be planned.
Do constraints affect performance? They add write checks and some create supporting indexes, but they also improve integrity and may help queries. Measure before changing valid rules.
Practical Review Checklist
- Define what one row represents before declaring constraints.
- Give every entity table a reliable Primary Key.
- Preserve natural candidate keys with UNIQUE and NOT NULL.
- Use Foreign Keys for relationships inside the database boundary.
- Mark mandatory attributes and relationships as NOT NULL.
- Use CHECK for precise same-row domain conditions.
- Combine DEFAULT with validation rather than treating it as validation.
- Define NULL, case, collation, and scope behavior explicitly.
- Name constraints consistently for errors and migrations.
- Inspect duplicates, orphans, and invalid values before adding rules.
- Choose cascade actions only after lifecycle analysis.
- Index Foreign Keys where real workloads benefit.
- Test valid, invalid, boundary, NULL, update, and concurrency cases.
- Confirm syntax and behavior in the selected DBMS.
Key Takeaway
Database constraints are the schema's safety layer. Primary Keys protect row identity, Foreign Keys protect relationships, Unique constraints prevent duplicate values, NOT NULL requires information, CHECK limits valid states, and DEFAULT supplies an omitted value.
CONSTRAINTS
= DATABASE-ENFORCED RULES
THAT PROTECT DATA INTEGRITY
Applications should still validate for usability and workflow, but the database must remain the final authority for invariants it can express. When constraints accurately match the table grain and business model, every API, script, import, and concurrent transaction shares the same definition of valid data.