You are currently viewing SQL 1.46 SQL Constraints

SQL 1.46 SQL Constraints

SQL Constraints

Welcome to the world of SQL! In this tutorial, we’ll delve into the concept of SQL Constraints. Constraints are rules that enforce the integrity of data in a SQL database. They help maintain the accuracy, consistency, and reliability of the data by defining certain conditions that must be met before data can be inserted, updated, or deleted in a table.

What are SQL Constraints?

SQL Constraints are rules that are enforced on columns in a table to ensure the accuracy and reliability of data. They are defined at the time of table creation or can be added later using ALTER TABLE statements. Constraints help maintain data integrity by enforcing certain conditions on the data stored in the table.

Types of SQL Constraints:

a. NOT NULL Constraint:


The NOT NULL constraint ensures that a column cannot have a NULL value. It enforces the presence of a value in a column.

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

In the above example, EmployeeID, FirstName, and LastName columns are defined with NOT NULL constraints, meaning these columns cannot contain NULL values.

b. UNIQUE Constraint:


The UNIQUE constraint ensures that all values in a column are unique and not duplicated.

CREATE TABLE Products (
    ProductID INT UNIQUE,
    ProductName VARCHAR(50) UNIQUE
);

In the above example, both ProductID and ProductName columns are defined with UNIQUE constraints, ensuring that no two rows can have the same values for these columns.

c. PRIMARY KEY Constraint:


The PRIMARY KEY constraint uniquely identifies each record in a table. It combines the NOT NULL and UNIQUE constraints.

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

In the above example, OrderID column is defined as the PRIMARY KEY, meaning it cannot contain NULL values and each value must be unique.

d. FOREIGN KEY Constraint:


The FOREIGN KEY constraint establishes a relationship between two tables. It ensures referential integrity by enforcing that the values in a column match the values in another table’s column.

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

In the above example, the CustomerID column in the Orders table is defined as a FOREIGN KEY, referencing the CustomerID column in the Customers table.

e. CHECK Constraint:


The CHECK constraint specifies a condition that must be true for each row in a table.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Age INT,
    CONSTRAINT CHK_Age CHECK (Age >= 18)
);

In the above example, the CHECK constraint ensures that the Age column contains only values greater than or equal to 18.

Implementation with Sample Codes:

Let’s create a sample database schema to demonstrate the implementation of SQL Constraints:

-- Create Tables
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) UNIQUE
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Insert Data
INSERT INTO Departments (DeptID, DeptName) VALUES (1, 'IT');
INSERT INTO Departments (DeptID, DeptName) VALUES (2, 'HR');

INSERT INTO Employees (EmployeeID, FirstName, LastName, DeptID) VALUES (1, 'John', 'Doe', 1);
INSERT INTO Employees (EmployeeID, FirstName, LastName, DeptID) VALUES (2, 'Jane', 'Smith', 2);
INSERT INTO Employees (EmployeeID, FirstName, LastName, DeptID) VALUES (3, 'David', 'Brown', NULL); -- This will violate the FOREIGN KEY constraint

-- Query Data
SELECT * FROM Departments;
SELECT * FROM Employees;

In the above SQL code:

  • We create two tables, Departments and Employees.
  • Departments table has a PRIMARY KEY constraint on DeptID and a UNIQUE constraint on DeptName.
  • Employees table has a PRIMARY KEY constraint on EmployeeID, NOT NULL constraints on FirstName and LastName, and a FOREIGN KEY constraint referencing DeptID in the Departments table.
  • We insert data into both tables, ensuring that the constraints are enforced.
  • The third insertion into the Employees table violates the FOREIGN KEY constraint because it tries to insert a NULL value into the DeptID column.

Conclusion


SQL Constraints are essential for maintaining data integrity in a database. By enforcing rules on columns, constraints ensure that the data remains accurate and consistent. SQL Constraints is fundamental for building robust and reliable database systems.

Leave a Reply