Constraints in SQL

In this article, we will explore various types of Constraints in SQL. Let’s get started.

Table of Contents

What are Constraints in SQL ?

Constraints in SQL are rules and conditions that you can apply to a database table column or a set of columns. These constraints help enforce data integrity, accuracy, and consistency within the database.

Types of Constraints in SQL

Let’s now explore different types of Constraints in SQL.

Primary Key Constraint

A Primary Key Constraint in SQL is a rule or condition applied to a database table column (or a set of columns) that serves a fundamental role in ensuring data integrity and uniqueness within the table. The primary key constraint enforces the following rules:

Uniqueness: Each value in the specified column(s) must be unique across all rows in the table. This means that no two rows can have the same value in the primary key column(s).
Not Null: The primary key column(s) cannot contain null values. Every row must have a non-null value in the primary key column(s).
Uniqueness Across Rows: The combination of values in the primary key column(s) must uniquely identify each row in the table.

The primary key constraint is commonly used to establish a unique identifier for each row in a table. This identifier is crucial for referencing and retrieving specific rows within the table efficiently.

Here’s an example of creating a table with a primary key constraint:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Unique Constraint

The unique constraint ensures that all values in a specified column (or set of columns) are unique, but it allows null values.

CREATE TABLE Customers (
    CustomerID INT UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Check Constraint

A check constraint defines a condition that values in a column must satisfy. It restricts the range of allowed values.

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT CHK_Amount CHECK (TotalAmount >= 0)
);

The purpose of this CHECK constraint, “CHK_Amount,” is to enforce a rule that ensures the “TotalAmount” column of the “Orders” table cannot contain negative values.

Default Constraint

A default constraint assigns a default value to a column if no value is specified during an insert operation.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2) DEFAULT 0.00
);

Foreign Key Constraint

A foreign key constraint establishes a relationship between two tables, ensuring referential integrity. It enforces that values in a column(s) match the values in another table’s primary key.

Here’s an example of creating a table with a foreign key constraint:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

n this example, the “Orders” table has a foreign key constraint on the “CustomerID” column, which references the “CustomerID” column in the “Customers” table. This ensures that every “CustomerID” value in the “Orders” table corresponds to an existing customer in the “Customers” table, maintaining referential integrity between the two tables.

Not Null Constraint

The not null constraint ensures that a column cannot contain null values.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL
);

Conclusion: Constraints in SQL

This article has provided a comprehensive overview of the various types of constraints in SQL and their critical roles in maintaining data integrity and consistency within a database. Constraints, such as Primary Key, Unique, Check, Default, Foreign Key, and Not Null, serve distinct purposes in shaping the behavior of database tables and columns.

The Primary Key Constraint ensures uniqueness, non-null values, and a unique identifier for each row.
The Unique Constraint guarantees uniqueness in column values while permitting null values.
A Check Constraint defines specific conditions that values must meet to be valid.
The Default Constraint assigns default values to columns when no explicit value is provided.
The Foreign Key Constraint establishes relationships between tables, enforcing referential integrity.
The Not Null Constraint mandates that a column must always contain non-null values.

By understanding and appropriately implementing these constraints, database designers and administrators can create robust and reliable database structures that prevent data inconsistencies and errors.

Related Articles :