SQL Constraints

In SQL, constraints are rules applied to table columns to ensure the accuracy, integrity, and reliability of the data stored in a database.
They control the type of data that can go into a table and prevent invalid or unwanted data from being inserted.

Think of constraints as traffic rules for your database – they guide what’s allowed and what’s not.

---

Why Use Constraints?

• Ensure Data Integrity – Prevents wrong or incomplete data.
• Maintain Accuracy – Ensures only valid values are stored.
• Avoid Duplicates – Stops repeated entries where not needed.
• Control Relationships – Maintains links between tables.

---

Types of SQL Constraints

SQL supports several constraints. Below are the most commonly used ones:

1. NOT NULL

• Ensures that a column cannot have a NULL value.
• Useful when you always require a value in a column.

Example:

CREATE TABLE Students (
    ID INT NOT NULL,
    Name VARCHAR(50) NOT NULL
);

Here, both ID and Name must have a value.

---

2. UNIQUE

• Ensures all values in a column are different.
• Allows NULL values unless combined with NOT NULL.

Example:

CREATE TABLE Employees (
    EmpID INT UNIQUE,
    Email VARCHAR(100) UNIQUE
);

No two employees can have the same EmpID or Email.

---

3. PRIMARY KEY

• Uniquely identifies each record in a table.
• Automatically NOT NULL and UNIQUE.
• A table can have only one primary key.

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50)
);

Each CustomerID must be unique and not null.

---

4. FOREIGN KEY

• Ensures a value in one table matches a value in another table’s primary key.
• Maintains referential integrity between tables.

Example:

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

Here, CustomerID in Orders must exist in Customers.

---

5. CHECK

• Ensures that the values in a column meet a specific condition.

Example:

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

Price must be greater than zero.

---

6. DEFAULT

• Assigns a default value if no value is provided during insert.

Example:

CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2) DEFAULT 0.00
);

If no balance is given, it will be 0.00.

---

7. INDEX (Not a true constraint, but often related)

• Improves search performance on columns.
• Does not enforce data rules, only speeds up queries.

---

Adding Constraints to an Existing Table

You can also add constraints after creating a table using ALTER TABLE:

ALTER TABLE Employees
ADD CONSTRAINT unique_email UNIQUE (Email);

---

Removing Constraints

If you need to remove a constraint:

ALTER TABLE Employees
DROP CONSTRAINT unique_email;

(Note: The exact syntax depends on the SQL database system – MySQL, SQL Server, PostgreSQL, etc.)

---

Key Points to Remember

• Constraints improve data quality by enforcing rules.
• You can combine constraints for stronger validation.
• Overusing constraints can make data entry harder – use wisely.
• Different database systems may have slightly different syntax.

---

In short: SQL constraints are the safety guards of a database. They prevent invalid data, maintain relationships, and ensure reliability — making them essential for robust database design.