Blog

SQL CONSTRAINT Keyword

SQL (Structured Query Language) is used to manage and manipulate relational databases. Constraints in SQL are rules applied to columns in a table to ensure the accuracy and reliability of the data. This tutorial covers the basics of SQL constraints, providing examples and step-by-step explanations.

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

1. Creating a Table with Constraints
2. Modifying Constraints
3. Dropping Constraints
4. Sample Codes and Scenarios

1. Introduction to SQL Constraints

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data within the database. Constraints can be specified when creating or altering a table.

2. Types of Constraints

NOT NULL Constraint

Ensures that a column cannot have a NULL value.

UNIQUE Constraint

Ensures all values in a column are different.

PRIMARY KEY Constraint

Uniquely identifies each record in a table.

FOREIGN KEY Constraint

Ensures referential integrity by linking two tables.

CHECK Constraint

Ensures that all values in a column satisfy a specific condition.

DEFAULT Constraint

Sets a default value for a column when no value is specified.

3. Creating a Table with Constraints

Example: Creating a Table with Various Constraints

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    BirthDate DATE,
    HireDate DATE DEFAULT GETDATE(),
    Salary DECIMAL(10, 2) CHECK (Salary > 0),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Explanation:

• EmployeeID INT PRIMARY KEY: EmployeeID is the primary key, ensuring each value is unique and not null.
• FirstName VARCHAR(50) NOT NULL: FirstName cannot be null.
• LastName VARCHAR(50) NOT NULL: LastName cannot be null.
• Email VARCHAR(100) UNIQUE: Email must be unique.
• HireDate DATE DEFAULT GETDATE(): If no hire date is provided, the current date is used.
• Salary DECIMAL(10, 2) CHECK (Salary > 0): Salary must be greater than 0.
• FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID): DepartmentID must exist in the Departments table.

4. Modifying Constraints

Example: Adding a Constraint

ALTER TABLE Employees
ADD CONSTRAINT chk_Salary CHECK (Salary >= 30000);

Explanation:

• This adds a check constraint to the Employees table ensuring that Salary is at least 30000.

Example: Dropping a Constraint

ALTER TABLE Employees
DROP CONSTRAINT chk_Salary;

Explanation:

• This drops the chk_Salary constraint from the Employees table.

5. Dropping Constraints

Constraints can be dropped if no longer needed. Use the ALTER TABLE statement to drop a constraint.

Example: Dropping a PRIMARY KEY Constraint

ALTER TABLE Employees
DROP CONSTRAINT PK_EmployeeID;

Explanation:

• This removes the primary key constraint from the EmployeeID column.

6. Sample Codes and Scenarios

Scenario 1: Ensuring Non-Null Values

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

Output:

• Trying to insert a product without a ProductName or with a Price less than or equal to 0 will fail.

Scenario 2: Enforcing Unique Values

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Name VARCHAR(100)
);

Output:

• Inserting duplicate emails will result in an error.

Scenario 3: Maintaining Referential Integrity

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

Output:

• Inserting an order with a CustomerID that does not exist in the Customers table will fail.

Conclusion

Understanding and using constraints in SQL ensures data integrity and reliability. This tutorial provided an overview of the most common constraints, examples of their usage, and detailed explanations. By applying these concepts, you can design robust databases that enforce data quality through constraints.