Constraints in SQL

Understanding SQL Constraints with Examples

Introduction to Constraints

Constraints are rules applied to database tables to ensure data integrity and maintain consistency. They define limits or conditions for the data stored in the tables.

Sample Table: Students

StudentID	Name	Age	Grade
101	Alice	20	A
102	Bob	22	B

1. PRIMARY KEY Constraint

The PRIMARY KEY uniquely identifies each record in a table.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade CHAR(1)
);
    

2. UNIQUE Constraint

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

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade CHAR(1),
    UNIQUE (Name)
);
    

3. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT,
    Grade CHAR(1)
);
    

4. FOREIGN KEY Constraint

The FOREIGN KEY constraint creates a link between two tables.

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    TeacherName VARCHAR(50)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade CHAR(1),
    TeacherID INT,
    FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
    

5. CHECK Constraint

The CHECK constraint enforces a condition on the data entered into a column.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age >= 18),
    Grade CHAR(1)
);
    

6. DEFAULT Constraint

The DEFAULT constraint assigns a default value if no value is specified for a column.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade CHAR(1) DEFAULT 'C'
);