Introduction
When you design a relational database, you are not just deciding what data to store — you are deciding what data is allowed to exist. SQL constraints are how you encode those decisions directly into the schema, so the database engine enforces them automatically, without relying on application code to do the job.
Three constraints sit at the heart of almost every database design: the Primary Key, the Foreign Key, and the Unique constraint. Understanding what each one does and why it exists is foundational knowledge for anyone working with SQL.
1. Primary Key
A Primary Key is the unique identifier for every row in a table. Think of it as the fingerprint of a record — no two rows can share the same primary key value, and no row can exist without one. Every well-designed table should have exactly one primary key. It can be a single column or a combination of columns, known as a composite primary key. The database engine automatically creates an index on this column, making lookups fast and efficient.
Rules:
• Values must be unique across every row in the table.
• A primary key column cannot hold a NULL value — ever.
• Only one primary key is allowed per table.
• Most databases automatically index the primary key for performance.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
-- AUTO_INCREMENT generates IDs automatically
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2)
);
In modern practice, most developers use an auto-incrementing integer or a UUID as the primary key. Both approaches ensure uniqueness without requiring manual management. The choice between them depends on your scale, distribution requirements, and whether you want keys to be sequential or opaque.
2. Foreign Key
A Foreign Key is the mechanism that connects two tables. It tells the database that a column's value must match a value already present in another table's primary key. This is the foundation of referential integrity — the guarantee that relationships between records remain consistent and valid at all times.
Without foreign keys, you could end up with order records pointing to customers who no longer exist, or invoice lines referencing products that were deleted. Foreign keys prevent exactly that kind of orphaned, broken data from entering the system.
Rules:
• The foreign key value must exist in the referenced table's primary key column, or be NULL.
• You cannot delete a parent row while child rows still reference it, unless a cascade rule is defined.
• The data types of the referencing and referenced columns must match. • Cascade options such as ON DELETE CASCADE or SET NULL control what happens when the parent record changes.
Example:
-- Parent table
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(80) NOT NULL
);
-- Child table with a Foreign Key
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
full_name VARCHAR(100),
dept_id INT,
CONSTRAINT fk_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
The ON DELETE SET NULL option means that if a department record is deleted, the dept_id in related employee rows is set to NULL rather than producing an error. ON DELETE CASCADE would instead automatically remove the child rows as well. Both are valid tools, but cascade deletions are permanent and should be used with care.
3. Unique Constraint
The Unique constraint ensures that no two rows in a table share the same value in a given column. Unlike a primary key, it permits NULL values in most database systems, and a single table can have multiple unique constraints. It is the right tool when a field must be distinct across all records, but is not the main identifier for the row.
Common use cases include email addresses, phone numbers, usernames, and national identification numbers — fields that should identify a person uniquely within the system, even though the row itself is identified by the primary key.
Rules:
• No two rows may share the same value in the constrained column.
• NULL values are generally permitted, though behaviour varies by database engine. • A table may have multiple unique constraints, unlike a primary key.
• The database engine automatically creates a unique index on the column. Example:
-- Unique on a single column
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(120) UNIQUE,
joined_at DATE
);
-- Composite unique constraint
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrolled_on DATE,
CONSTRAINT uq_enrollment
UNIQUE (student_id, course_id)
);
The composite unique example is worth noting. Individually, a student ID or course ID can appear in multiple rows. But the combination of both must remain unique, ensuring a student cannot enroll in the same course twice. This kind of business rule is far more reliably enforced at
the database level than in application code.
Quick Comparison
Conclusion
These three constraints work best together. In a typical relational database, every table receives a primary key. Tables that relate to one another use foreign keys to connect those relationships securely. Any column that must remain distinct but is not the main identifier gets a unique constraint.
Designing with constraints from the start is one of the highest-value habits a developer can adopt. It moves data quality responsibilities from scattered application code into the database itself — the single layer every interaction with your data must pass through. Bad data becomes structurally impossible, not just unlikely.
Master these three constraints, and you will have the foundation to model almost any real-world dataset clearly, correctly, and with confidence.
Frequently Asked Questions (FAQs):
1. What are SQL Constraints?
SQL Constraints are rules applied to table columns in a database to ensure the accuracy, reliability, and integrity of the data. They restrict the type of data that can be inserted into a table and help maintain consistency.
2. What are the types of SQL Constraints?
Common types of SQL Constraints include:
- NOT NULL – Ensures a column cannot have NULL values
- UNIQUE – Ensures all values in a column are different
- PRIMARY KEY – Uniquely identifies each record
- FOREIGN KEY – Links two tables together
- CHECK – Validates data based on a condition
- DEFAULT – Assigns a default value
3. Why are SQL Constraints important?
SQL Constraints are important because they maintain data integrity, prevent invalid data entry, enforce business rules, and ensure consistency across the database.
4. What is the difference between a PRIMARY KEY and a UNIQUE constraint?
A PRIMARY KEY uniquely identifies each record and does not allow NULL values, while a UNIQUE constraint also ensures uniqueness but can allow one NULL value (depending on the database system).
5. Can we use multiple constraints on a single column?
Yes, multiple SQL Constraints can be applied to a single column. For example, a column can have both NOT NULL and UNIQUE constraints to ensure it always contains unique and non-empty values.
Related Links:
Java Design Patterns You Should Learn
Do visit our channel to know more: SevenMentor
Author:-
Pooja Ghodekar
Pooja Ghodekar
Expert trainer and consultant at SevenMentor with years of industry experience. Passionate about sharing knowledge and empowering the next generation of tech leaders.