The Developer Space

3 0

Introduction

SQL Constraints are rules enforced on data columns in a database table. They help ensure the accuracy, consistency, and reliability of the data stored in a database. In this sixth part of our SQL for Beginners series, we’ll explore the various types of SQL constraints, learn how they help maintain data integrity, and see examples of how they are used in practice.

Articles in the Series

Before reading this article, I recommend that you read the previous articles in this series to understand the basics and to set up the Sample database that we’ll be using for the examples in the article.

SQL for Beginners: Learn the Basics with Examples

SQL for Beginners – Part 2: SELECT Commands with Examples

SQL for Beginners – Part 3: JOINS

SQL for Beginners – Part 4: Mastering Subqueries

SQL for Beginners – Part 5: VIEWS

SQL for Beginners – Part 6: SQL Constraints and Data Integrity

Setting up the Sample Database

The examples in these article series were created using PostgreSQL (Database) and DbStudio (IDE). However, you can try out these examples in any Relational Database. You may have to tweak the syntax to make it work with your target database platform.

Access code samples in this article here: https://github.com/The-Developer-Space/CodeSamples/blob/main/SQL_for_Beginners

What Are Constraints?

Constraints are rules applied to table columns that restrict the type of data that can be stored. They are defined during table creation (or later via ALTER TABLE) and help enforce business rules at the database level.

Data Integrity in Relational Databases vs NoSQL Databases

Relational databases maintain data integrity through a strict schema model that enforces rules such as primary keys, foreign keys, unique constraints, not null constraints, and check constraints. These built-in mechanisms ensure that the data adheres to defined relationships and business rules, preventing duplicate entries, enforcing referential integrity, and validating data types and ranges.

In contrast, NoSQL databases typically offer more flexibility with schema-less or schema-on-read approaches, prioritizing scalability and performance over rigid data consistency. While some NoSQL databases do support validation rules or limited integrity features, they often rely on the application layer to enforce data integrity, which can increase complexity and risk of inconsistent data if not carefully managed.

Why Constraints Matter for Data Integrity

Constraints are the gatekeepers of your database. They prevent incorrect, incomplete, or inconsistent data from entering your tables. Here’s how they help:

Prevent Invalid Data

Constraints such as NOT NULL or CHECK ensure that only acceptable values are entered. For example, a user’s age must be greater than zero.

Maintain Uniqueness

The UNIQUE and PRIMARY KEY constraints ensure no duplicate entries exist where they shouldn’t, such as email addresses or user IDs.

Enforce Relationships

FOREIGN KEY constraints ensure that relationships between tables are valid. For example, an order must belong to an existing customer.

Improve Data Quality

By enforcing rules early (at the database level), you reduce the likelihood of errors and the need for costly data cleaning later.

Enhance Query Accuracy

Accurate and consistent data improves the results of your SQL queries, analytics, and reporting.

Protect Business Logic

Constraints act as a first line of defense by enforcing critical rules even before application-level validations kick in.

Support Compliance

In regulated industries, data integrity rules are not just good practice. They’re a requirement. SQL constraints help ensure that your data meets these standards.

In short, constraints are the immune system of your database. They stop bad data from entering and help ensure that the information you rely on remains clean, accurate, and meaningful.

Providing a name for Constraints

Providing a name for constraints in a relational database is important for clarity, maintainability, and easier debugging. Every constraint must have a name. If you do not provide a name when creating a constrains, the database engine automatically assigns a system-generated name to it. When you assign meaningful names to constraints (such as CHK_MinAge or FK_Order_CustomerID), it becomes easier to understand the purpose of each rule without digging deep into the schema. This is especially valuable in large databases with many constraints and relationships. Moreover, when a constraint is violated during data operations, the database engine includes the constraint name in the error message. If the constraint has a clear, custom name, developers can quickly identify and resolve the issue. On the other hand, relying on system-generated names (like SYS_C007643) makes debugging harder and complicates schema updates or migrations. Consistent and descriptive naming also helps with documentation, collaboration, and automated scripts for schema management.

Constraint Naming Conventions

When naming constraints in a relational database, following consistent naming conventions greatly enhances readability and maintainability. A common convention is to prefix the constraint type followed by the table and column it applies to. For example, PK_Users for a primary key on the Users table, FK_Orders_CustomerID for a foreign key on Orders.CustomerID, or CHK_Employees_Age for a check constraint validating an employee’s age. This pattern immediately informs developers and DBAs about the constraint’s purpose and location. Using uppercase for prefixes (e.g., PK, FK, CHK, UQ, DF) makes them easily identifiable in schema definitions. Avoid using spaces or special characters, and keep names concise but meaningful. Adhering to naming conventions also facilitates automated schema generation and version control, making team collaboration smoother and reducing the likelihood of errors during deployments or migrations.

Types of Constraints

NOT NULL

Ensures a column cannot have a NULL value. This constraint guarantees that a column must contain a value. It’s especially useful for critical information such as names, emails, or primary identifiers, where absence of data would cause problems.

CREATE TABLE Users (
    UserID INT NOT NULL,
    UserName VARCHAR(100) NOT NULL
);

UNIQUE

Ensures all values in a column are different. The UNIQUE constraint is used when a column should not contain duplicate values. This is essential for fields like usernames, product SKUs, or license numbers where duplication would lead to ambiguity.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    SKU VARCHAR(255) CONSTRAINT UQ_Products_SKU UNIQUE
);

PRIMARY KEY

A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table. The PRIMARY KEY ensures each row in the table has a unique, non-null identifier. It’s the foundation of relational database design, allowing you to reference data from other tables accurately.

CREATE TABLE Products (
    ProductID INT CONSTRAINT PK_Products PRIMARY KEY,
    ProductName VARCHAR(255)
);

FOREIGN KEY

Ensures a value in one table matches a value in another table. Enforces referential integrity. A FOREIGN KEY creates a link between two tables by referencing the PRIMARY KEY of another. It ensures that the value exists in the referenced table, preserving referential integrity.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CHECK

Ensures that values in a column meet a specific condition. The CHECK constraint allows you to enforce business logic directly at the database level. For instance, it ensures only positive money values are inserted.

A CHECK constraint can also refer to several columns. Say you store a regular price and a discounted price, and you want to ensure that the discounted price is lower than the regular price

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10, 2) CONSTRAINT CHK_Products_Price CHECK (Price > 0),
    DiscountedPrice DECIMAL(10, 2) CONSTRAINT CHK_Products_DiscountedPrice CHECK (DiscountedPrice > 0),
    CONSTRAINT CHK_Products_Price_gt_DiscountedPrice CHECK (Price > DiscountedPrice)
);

DEFAULT

Assigns a default value if none is provided during insert. The DEFAULT constraint automatically assigns a value to a column if none is provided during an insert. It’s useful for status fields, timestamps, or any field with a common default.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE CONSTRAINT DF_Orders_OrderDate DEFAULT CURRENT_DATE
);

Composite Keys

A composite key is a combination of two or more columns in a table that together uniquely identify a row. Unlike a single-column primary key, where one column alone ensures uniqueness, a composite key uses the combined values of multiple columns to enforce row-level uniqueness.

This is particularly useful when no single column can uniquely identify a record on its own. Composite keys are often used in junction tables or association tables that model many-to-many relationships in relational databases.

Example:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderID, ProductID)
);

Creating Constraints on existing Columns

Constraints can also be added after table creation. If existing data on the column violates the new constraint, your database engine will reject the ALTER statement.

ALTER TABLE Orders ADD CONSTRAINT CHK_Orders_Amount CHECK (Amount > 0);

Some database engines allow you to add a constraint to existing columns without validating existing data that may violate the constraint. For example, PostgreSQL uses the NOT VALID clause that creates the constraint without checking exiting data for violations. You can clean up the data manually and later run the ALTER TABLE command with the VALIDATE CONSTRAINT clause to validate the constraint. Note that the NOT VALID clause is applied to existing rows only. The constraint is applied during insertion of new rows or updates to existing rows.

-- Create the constrain without checking existing data
ALTER TABLE Orders ADD CONSTRAINT CHK_Orders_Amount CHECK (Amount > 0);
--Throws 23514: check constraint "chk_orders_amount" of relation "orders" is violated by some row

-- Create the constraint without checking existing data
ALTER TABLE Orders ADD CONSTRAINT CHK_Orders_Amount CHECK (Amount > 0) NOT VALID;

--Validate constraint
ALTER TABLE Orders VALIDATE CONSTRAINT CHK_Orders_Amount;

Different database engines support this feature in different ways. Check the official documentation to learn more.

Real-World Example: Ensuring Data Integrity in an E-commerce Database

CREATE TABLE Customers (
    CustomerID INT CONSTRAINT PK_Customers PRIMARY KEY,
    Email VARCHAR(100) CONSTRAINT UQ_Products_Email UNIQUE NOT NULL
);

CREATE TABLE Products (
    ProductID INT CONSTRAINT PK_Products PRIMARY KEY,
    Price DECIMAL(10, 2) CONSTRAINT CHK_Products_Price CHECK (Price > 0)
);

CREATE TABLE Orders (
    OrderID INT CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE CONSTRAINT DF_Orders_OrderDate DEFAULT CURRENT_DATE,
    CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Constraints Violation

What happens when you insert or update data that violates a constraint? Let’s try to insert duplicate email into the Customers table in the above example.

INSERT INTO Customers (CustomerID, Email)
VALUES (1, 'john.doe@example.com');

INSERT INTO Customers (CustomerID, Email)
VALUES (2, 'john.doe@example.com');

You should get an error similar to this. The actual error message depends on your database engine. Creating constraints on your columns ensures that no invalid data finds its way into your database no matter from where the data is being inserted – your IDE, from your query tool, or from your application code – your database engine will simply reject it and ensure data integrity is preserved.

23505: duplicate key value violates unique constraint "uq_products_email"

Note that the error message specifies the constraint name that was violated. Had you not provided a name to the constraint when you created it, it would have returned a system-generated name that would have made it hard for you to resolve the issue.

Disabling Constraints on a Column

To temporarily disable a Constraint on a table column, use the ALTER TABLE command with the DISABLE CONSTRAINT clause. Once a constraint is disabled, it is not enforced when data is inserted or updated, until the constraint is enabled with the ENABLE CONSTRAINT clause.

-- Disable the constraint 
ALTER TABLE Orders DISABLE CONSTRAINT CHK_Orders_Amount;

--Insert data that violate the constraint
INSERT INTO Orders (....) VALUES (.....)

--Enable the constraint
ALTER TABLE Orders ENABLE CONSTRAINT CHK_Orders_Amount;

Note that different database engines have different levels of support and different syntax for disabling constraints. Check the official documentation to learn more.

Removing Constraints from a Column

To permanently remove a Constraint from a table column, use the ALTER TABLE command with the DROP CONSTRAINT clause. Once a constraint is dropped from a table column, it is no longer enforced when data is inserted or updated.

-- Drop the constraint 
ALTER TABLE Orders DROP CONSTRAINT CHK_Orders_Amount;

Common Errors Related to Constraints

• Inserting NULL into a NOT NULL column
• Duplicate values in a UNIQUE column
• Violating a FOREIGN KEY relationship
• Failing a CHECK condition

Summary

• SQL constraints ensure data validity and consistency.
• Use NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT to enforce rules.
• Apply constraints wisely to avoid invalid or corrupt data.
• Always test constraint behavior with sample data.

References

PostgreSQL Constraints

MySQL Constraints

SQL Server Constraints

Oracle Constraints