What are database constraints?

Database constraints are fundamental rules enforced on data columns in a table. Their primary purpose is to maintain the accuracy, consistency, and reliability of the data within a relational database, preventing the insertion of invalid or inconsistent data.

What are Database Constraints?

Database constraints are rules that are applied to the data in a table's columns. They define the permissible data values and relationships between data, ensuring that only valid data is entered into the database. These rules can be applied at the column level (e.g., NOT NULL for a single column) or at the table level (e.g., a PRIMARY KEY spanning multiple columns).

Why are Database Constraints Important?

The importance of constraints lies in their ability to guarantee data integrity. They serve several critical functions:

• Data Accuracy: Prevents incorrect data from being entered, such as duplicate primary keys or null values in mandatory fields.
• Data Consistency: Ensures that relationships between tables are maintained (e.g., FOREIGN KEY constraints), preventing orphaned records.
• Business Rule Enforcement: Helps implement specific business rules directly at the database level, such as an age column always being positive.
• Data Reliability: Leads to more reliable applications, as data stored in the database adheres to predefined standards.
• Performance Optimization: Indexes created by PRIMARY KEY and UNIQUE constraints can significantly speed up data retrieval operations.

Common Types of Database Constraints

SQL provides several types of constraints to enforce different kinds of data integrity. Here are the most common ones:

NOT NULL Constraint

Ensures that a column cannot have a NULL value. This means every row must have a value for that column.

CREATE TABLE Employees (
    ID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50)
);

UNIQUE Constraint

Ensures that all values in a column (or a group of columns) are different. While multiple columns can have a UNIQUE constraint, all values in that specific column(s) must be unique. It allows NULL values, but only one NULL if not combined with NOT NULL.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    SKU VARCHAR(20) UNIQUE
);

PRIMARY KEY Constraint

A PRIMARY KEY is a column or a set of columns that uniquely identifies each row in a table. It cannot contain NULL values and must contain UNIQUE values. Each table can have only one PRIMARY KEY.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

FOREIGN KEY Constraint

A FOREIGN KEY is a column or a set of columns in one table that refers to the PRIMARY KEY (or UNIQUE key) in another table. It establishes a link between two tables, enforcing referential integrity.

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

CHECK Constraint

Ensures that all values in a column satisfy a specific condition. It allows you to define a boolean expression that must evaluate to true for any value inserted or updated in that column.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2) CHECK (Price >= 0)
);

DEFAULT Constraint

Provides a default value for a column when no value is specified during an INSERT operation. If a value is explicitly provided, the default value is overridden.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    HireDate DATE DEFAULT GETDATE(),
    Status VARCHAR(20) DEFAULT 'Active'
);