RDBMS SQL Constraints

SQL Constraints in RDBMS

When storing information in a database, it is important to make sure the data is correct, complete, and reliable. Imagine a student database where two students have the same ID, or an employee record has no name.

To prevent these issues, SQL provides constraints.

SQL constraints are rules applied to table columns that control what kind of data can be stored. They help maintain data accuracy, consistency, and integrity by preventing invalid or duplicate information from entering the database.

Constraints are usually added when a table is created, but they can also be added later if needed.

Why Do We Need Constraints?

Without constraints, users could accidentally enter incorrect or incomplete information.

For example:

• Two customers might have the same customer ID.
• A product could have a negative price.
• An order could refer to a customer who does not exist.
• Important fields like names or email addresses could be left empty.

Constraints help avoid these problems and ensure that only valid data is stored.

Some benefits of using constraints include:

• Improves data accuracy.
• Prevents duplicate records.
• Maintains relationships between tables.
• Ensures required fields are filled.
• Reduces data entry errors.

Common Types of SQL Constraints

There are several types of SQL constraints used in relational databases.

PRIMARY KEY

A primary key uniquely identifies each record in a table. Every value in a primary key must be unique, and it cannot contain NULL values.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

In this table, every student has a unique StudentID. Two students cannot share the same ID, and the ID cannot be left empty.

FOREIGN KEY

A foreign key creates a relationship between two tables. It ensures that a value exists in another table before it can be stored.

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

This prevents an order from being created for a customer who does not exist in the Customers table.

NOT NULL

The NOT NULL constraint ensures that a column must always contain a value.

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100) NOT NULL
);

Here, every employee must have a name. Leaving the Name column empty is not allowed.

UNIQUE

The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

Every user must have a different email address. Unlike a primary key, a table can have multiple UNIQUE constraints on different columns.

CHECK

The CHECK constraint ensures that data meets a specific condition before it is stored.

CREATE TABLE Products (
    ProductID INT,
    Price DECIMAL(10,2),
    CHECK (Price > 0)
);

This rule prevents products from having a price of zero or a negative value. You can also use CHECK constraints for values such as age, quantity, or marks.

DEFAULT

The DEFAULT constraint automatically inserts a value if none is provided.

CREATE TABLE Employees (
    EmployeeID INT,
    Status VARCHAR(20) DEFAULT 'Active'
);

If no status is entered, the database automatically stores Active. This helps reduce repetitive data entry.

Using Multiple Constraints

A table can use several constraints together.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 5)
);

In this example, StudentID must be unique, Name cannot be empty, Email must be different for every student, and Age must be at least 5 years.

Using multiple constraints keeps the data clean and reliable.

Best Practices

When designing database tables, follow these good practices:

• Use a primary key for every table.
• Apply NOT NULL to required fields.
• Use UNIQUE for values like email addresses or usernames.
• Add CHECK constraints to validate numbers and ranges.
• Use DEFAULT values where appropriate.
• Define foreign key constraints to maintain relationships between tables.