This website use cookies to help you have a superior and more admissible browsing experience on the website.
Loading...
By:
SQL constraints are rules enforced directly by the database engine to maintain data quality. They act as guardrails at the schema level, ensuring every row meets specific criteria before it is written to storage.
Experienced developers rely on them because application-level validation is never fully reliable. A manual SQL script, a middleware bug, or a direct database connection can all bypass your application logic. Constraints applied at the database layer provide a hard stop, keeping your source of truth clean regardless of how data enters the system.
You can define constraints in two ways:
CREATE TABLE statement when the table is first built.ALTER TABLE command.Constraints also operate at different scopes:
employee_id and project_id is unique across a row.SQL Constraints vs. Indexes
Constraints and indexes are often confused, but they serve different purposes. Constraints enforce data integrity by validating or restricting values, while indexes improve query performance by making data retrieval faster.
In short, constraints protect data quality, while indexes optimize access to data.
Here’s a quick overview of the most common SQL constraints, what they do, and where they’re typically used.
| Constraint | Purpose | Common Use Case |
|---|---|---|
| NOT NULL | Prevent missing values | User email, order date |
| UNIQUE | Prevent duplicate values | Username, SSN, SKU |
| PRIMARY KEY | Unique row identifier | Employee ID, Product ID |
| FOREIGN KEY | Maintain relationships | Department mapping, customer orders |
| CHECK | Enforce value rules | Salary > 0, age ≥ 18 |
| DEFAULT | Set fallback values | Active status, created date |
The following examples use a consistent employees and departments schema to show how each constraint works in a realistic setting.
The NOT NULL constraint prevents a column from storing a NULL value, meaning the field must always contain data. This is critical for columns your application logic depends on, such as identifiers, status fields, and contact information.
-- Standard SQL
CREATE TABLE departments (
dept_id INT NOT NULL,
dept_name VARCHAR(100) NOT NULL
);
Common use cases: employee names, email addresses, timestamps, and status fields.
A common mistake is leaving foreign key columns nullable. This can produce orphaned records — rows that reference a parent that no longer exists or was never set.
The UNIQUE constraint ensures every value in a column is distinct. Unlike a primary key, a table can have multiple UNIQUE constraints — and unlike NOT NULL, a UNIQUE column can store NULL values (behavior varies slightly by platform).
-- Standard SQL
CREATE TABLE employees (
employee_id INT NOT NULL,
email VARCHAR(255) UNIQUE,
social_security_number VARCHAR(11) CONSTRAINT unq_ssn UNIQUE
);
You can also apply UNIQUE at the table level to enforce uniqueness across a combination of columns — for example, preventing the same employee from being assigned to the same project more than once:
-- Composite unique constraint
ALTER TABLE project_assignments
ADD CONSTRAINT unq_emp_project UNIQUE (employee_id, project_id); 
A primary key uniquely identifies each record in a table. It combines the behavior of NOT NULL and UNIQUE — the column must contain a value, and that value must be different for every row.
Every well-designed table should have exactly one primary key. Without it, you have no reliable way to reference or update a specific row.
-- Standard SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
); 
A FOREIGN KEY links a column in one table to the primary key of another. It enforces referential integrity — meaning you cannot insert a value that doesn’t exist in the parent table, and you cannot delete a parent row while child rows still reference it (by default).
-- Standard SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE CASCADE
);
Two common deletion behaviors to know:
Choose based on your data model. Cascade is convenient but can cause unintended data loss if not applied carefully.
The CHECK constraint validates column values against a Boolean expression. Any row that fails the condition is rejected. This lets you enforce business rules directly at the database level, rather than relying on application code.
-- Standard SQL
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);
ALTER TABLE employees
ADD CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01'); 
CHECK constraints and NULL values interact in a non-obvious way. If a column value is NULL, the expression evaluates to UNKNOWN — and the row is allowed through. If NULL should be rejected, combine CHECK with NOT NULL.The DEFAULT constraint assigns a fallback value when an INSERT statement omits a column. This simplifies application code by handling optional fields at the database level.
-- Standard SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
is_active BOOLEAN DEFAULT TRUE,
country VARCHAR(50) DEFAULT 'USA'
);
Use defaults for values that are genuinely predictable — like a status flag or a known region. Avoid placeholder dates like '1900-01-01' or arbitrary numbers. If the data is truly unknown, NULL is the more honest choice and won’t skew your reporting.
Database schemas are rarely static. As your application evolves, you will need to adjust existing rules or introduce new ones to match changing business requirements.
The cleanest approach is to define your constraints inside the CREATE TABLE statement. This ensures no invalid data can enter the system from day one.
-- Standard SQL
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
email VARCHAR(255) CONSTRAINT unq_emp_email UNIQUE,
salary DECIMAL(10, 2) CONSTRAINT chk_min_wage CHECK (salary > 0)
); 
In production environments, you will often need to introduce new rules on tables that already contain data. Use the ALTER TABLE command to do this.
If existing rows violate the new rule, the database will reject the operation entirely — so it is worth auditing your data before adding a constraint to a live table.
-- Adding a foreign key to an existing table
ALTER TABLE employees
ADD CONSTRAINT fk_employee_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id); 
When a business rule changes, use DROP CONSTRAINT to remove it. You must reference the constraint by its exact name — which is another reason naming conventions matter (covered in the next section).
-- Standard SQL
ALTER TABLE employees
DROP CONSTRAINT chk_min_wage; 
DROP FOREIGN KEY rather than DROP CONSTRAINT.One of the most common mistakes developers make is letting the database engine auto-generate constraint names. The result is something like SYS_C001023 — which tells you nothing when it appears in an error log.
Use a consistent naming convention from the start:
pk_employees: primary key on the employees tablefk_employees_dept: foreign key from employees to departmentschk_employees_salary: check constraint on the salary columnunq_employees_email: unique constraint on the email columnWhen a “constraint violated” error surfaces in production, a readable name tells you exactly which rule was broken and on which table — no guesswork required.
The SQL standard defines how constraints should behave, but each database engine has its own quirks. These differences matter most when you are migrating between platforms or maintaining applications that run on more than one database.
At a glance:
| Feature | MySQL (8.0.16+) | PostgreSQL | SQL Server |
|---|---|---|---|
| CHECK Constraints | Enforced | Enforced | Enforced |
| UNIQUE with multiple NULLs | Allowed | Allowed | Not allowed |
| Deferrable Constraints | No | Yes | No |
| Skip historical validation | No | NOT VALID | WITH NOCHECK |
| PRIMARY KEY required | Yes (InnoDB) | No (recommended) | No (recommended) |
The sections below explain each difference in detail.
For years, MySQL accepted CHECK constraint syntax without actually enforcing it. The engine parsed the statement and moved on — no error, no validation. This changed in version 8.0.16, released in 2019, when MySQL began enforcing CHECK constraints properly.
If you are working with a legacy MySQL database, this is worth investigating. Data entered before the upgrade may not meet the rules your schema now enforces.
PostgreSQL offers the most complete constraint support of the three platforms. Its standout feature is deferrable constraints, which let you temporarily suspend a constraint during a transaction and check it only at COMMIT.
This is particularly useful for bulk data imports or operations where rows must be inserted in a specific order before referential integrity can be satisfied.
-- PostgreSQL: create a deferrable foreign key
ALTER TABLE employees
ADD CONSTRAINT fk_employee_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
DEFERRABLE INITIALLY DEFERRED; 
PostgreSQL also supports NOT VALID, which lets you add a constraint to an existing table without validating historical rows — useful for large production tables where a full table scan would cause downtime.
-- PostgreSQL: add constraint without checking existing rows
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0) NOT VALID;
-- Validate historical rows separately, during a low-traffic window
ALTER TABLE employees VALIDATE CONSTRAINT chk_salary; 
A common source of confusion when switching platforms is how NULL values interact with UNIQUE constraints.
In MySQL and PostgreSQL, multiple NULLs are allowed in a unique column — because NULL represents an unknown value, and no two unknowns are considered equal. SQL Server takes a different position: a UNIQUE constraint permits only one NULL. Any additional NULL is treated as a duplicate and rejected.
SQL Server also supports WITH NOCHECK, which is its equivalent of PostgreSQL’s NOT VALID — allowing you to add a constraint to a table without validating existing rows:
-- SQL Server: add constraint without validating existing data
ALTER TABLE employees
WITH NOCHECK ADD CONSTRAINT chk_salary CHECK (salary > 0); 
SQL constraints protect data within a single database instance. But they cannot guard against server failure, replication lag, or consistency gaps when the same data needs to exist across multiple systems.
For enterprises running distributed databases or real-time analytics pipelines, schema-level rules are necessary but not sufficient. That is where i2Stream comes in.
i2Stream is an enterprise-grade database replication solution that ensures data remains consistent as it moves between systems. Whether you are replicating to a disaster recovery site, migrating to a new platform, or feeding a data warehouse.
i2Stream covers the replication layer. For teams that need broader coverage, Info2soft also offers i2Backup for centralized backup across physical, virtual, and cloud environments, and i2Availability for automated failover and high availability.
SQL constraints define what valid data looks like. i2Stream ensures it stays that way across every system it touches.
Q1: What is the difference between a PRIMARY KEY and a UNIQUE constraint?
Both enforce uniqueness, but they behave differently in two ways. A table can only have one primary key, and it never allows NULL values. A table can have multiple UNIQUE constraints, and depending on the database engine, a unique column may allow one or more NULLs.
Q2: Do constraints affect database performance?
Yes, but the impact is minor. Every INSERT, UPDATE, or DELETE triggers a validation check, which adds a small overhead. In practice, this cost is far outweighed by the time saved from not having to clean up corrupted data. It is also worth noting that PRIMARY KEY and UNIQUE constraints automatically create indexes, which actually speed up read queries.
Q3: How do I view existing constraints on a table?
In most databases, you can query the information_schema.table_constraints view to list all active constraints. In SQL Server specifically, running sp_help 'table_name' returns a detailed breakdown of every rule applied to that table.
Q4: Should I use constraints if my application already validates data?
Yes, always. Application-level validation improves user experience, but it can be bypassed — by a direct database connection, a manual SQL script, or a bug in your code. Constraints enforced at the database level are the final backstop that protects your data regardless of how it enters the system.
Q5: Can I add a NOT NULL constraint to a column that already has data?
Yes, but only if the column contains no NULL values. If any NULLs exist, the ALTER TABLE command will fail. You will need to run an UPDATE first to replace all NULL entries with valid values before the constraint can be applied.
SQL constraints are one of the most underused tools in database design. Used correctly, they catch bad data before it enters the system, saving cleanup time and preventing the kind of silent errors that are hardest to debug.
The six types covered in this guide each serve a distinct purpose, from enforcing required fields with NOT NULL to maintaining table relationships with FOREIGN KEY. The key habits to build are simple: always name your constraints, audit existing data before applying rules to a live table, and understand how your specific database engine handles edge cases like NULLs and CHECK enforcement.
Constraints protect data within a single instance. For teams managing data across multiple systems or environments, pairing them with a replication and backup strategy gives you end-to-end coverage. Info2soft offers a full suite of solutions for data protection, replication, and migration — built for enterprise environments where data integrity cannot be compromised.
