SQL Create Constraints

SQL constraints are rules applied to the data in a table to ensure its accuracy, consistency, and integrity. They are used to enforce specific conditions on data and prevent invalid entries, maintaining the reliability of your database. Constraints can be applied either when a table is created or modified later using SQL commands.

Why Use SQL Constraints?

• Ensure Data Integrity: Prevent invalid or duplicate data entries.
• Enhance Reliability: Maintain the consistency of data across tables.
• Enforce Business Rules: Ensure that only valid data aligns with specific requirements.
• Avoid Manual Errors: Automatically enforce data restrictions at the database level.

Types of SQL Constraints

The following are the most commonly used SQL constraints:

1. NOT NULL

• Ensures that a column cannot have a NULL value.
• Prevents missing or undefined values in critical fields.

Example:

1
2
3
4
CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    LastName VARCHAR(100) NOT NULL
);

2. UNIQUE

• Ensures that all values in a column are unique.
• Prevents duplicate entries for the specified column.

Example:

1
2
3
4
CREATE TABLE Employees (
    EmployeeID INT NOT NULL UNIQUE,
    Email VARCHAR(255) UNIQUE
);

3. PRIMARY KEY

• A combination of NOT NULL and UNIQUE.
• Uniquely identifies each row in a table.
• Only one primary key can be defined per table.

Example:

1
2
3
4
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName VARCHAR(100)
);

4. FOREIGN KEY

• Creates a relationship between two tables.
• Ensures that the value in a column matches a value in another table.

Example:

1
2
3
4
5
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. CHECK

• Ensures that values in a column meet specific conditions.

Example:

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

6. DEFAULT

• Assigns a default value to a column if no value is provided.

Example:

1
2
3
4
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Country VARCHAR(50) DEFAULT 'USA'
);

7. CREATE INDEX

• Improves the speed of data retrieval.
• Does not enforce data rules but optimizes query performance.

Example:

1
CREATE INDEX idx_lastname ON Employees (LastName);

Benefits of Using SQL Constraints

• Prevent Data Inconsistencies: Constraints enforce rules that maintain the integrity of the database.
• Reduce Application Errors: By defining rules in the database layer, constraints prevent invalid data entry.
• Improve Data Accuracy: Constraints ensure compliance with predefined business rules.
• Optimize Database Performance: Constraints like indexes speed up data retrieval.

Key Points to Remember

• Constraints are rules that maintain data integrity and accuracy.
• They can be applied at the column level (specific to a column) or table level (affecting multiple columns or tables).
• Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and CREATE INDEX.
• Constraints can be added when creating a table or later using the ALTER TABLE statement.
• Proper use of constraints helps prevent errors and maintain the reliability of your database.