SQL Constraints

Constraints are rules that control the data entered into a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Here, PRIMARY KEY and NOT NULL are constraints.

Constraints help prevent invalid or unwanted data from being stored.

• Prevent duplicate values
• Prevent missing required values
• Maintain relationships between tables
• Restrict incorrect values
• Provide default values

Common SQL constraints include:

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

Each constraint has a different purpose.

A PRIMARY KEY uniquely identifies each record in a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Each student must have a unique student ID.

A FOREIGN KEY creates a relationship between two tables.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The course_id connects the students table with the courses table.

The UNIQUE constraint prevents duplicate values in a column.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

Two students cannot have the same email value in this column.

The NOT NULL constraint requires a column to have a value.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

The name cannot be NULL.

The DEFAULT constraint provides a value automatically when no value is supplied.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'Active'
);

If status is not specified, the default value can be Active.

The CHECK constraint restricts values based on a condition.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

This requires the age to be at least 18.

A column can have more than one constraint.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE
);

The email must be provided and must also be unique.

Constraints can be defined while creating a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

The constraints are created together with the table.

A constraint can be written directly after a column definition.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18)
);

These are examples of column-level constraints.

Some constraints can be defined separately at the table level.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    CONSTRAINT pk_students
    PRIMARY KEY (student_id)
);

The primary key constraint is defined separately from the column definition.

You can give a constraint a specific name.

CREATE TABLE students (
    student_id INT,
    CONSTRAINT pk_students
    PRIMARY KEY (student_id)
);

Here, the primary key constraint is named pk_students.

If a column is NOT NULL, you should provide a value when inserting a record.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO students (student_id, name)
VALUES (101, 'Rahul');

The name value is provided, so the record can be inserted.

A UNIQUE constraint prevents duplicate values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

If an email already exists, inserting the same email again can cause a constraint violation.

A DEFAULT value can be used automatically when the column is not specified.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'Active'
);

INSERT INTO students (student_id)
VALUES (101);

The status can automatically receive the default value Active.

A CHECK constraint can prevent invalid values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

An insert with an age below the defined condition may be rejected by the database.

A PRIMARY KEY identifies records uniquely and cannot contain NULL values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Every record needs a valid primary key value.

A FOREIGN KEY helps maintain a valid relationship between related tables.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The course_id in students references course_id in courses.

Constraints can also be added to an existing table using ALTER TABLE.

ALTER TABLE students
ADD CONSTRAINT uq_email
UNIQUE (email);

This adds a UNIQUE constraint to the email column.

You can add a primary key to an existing table.

ALTER TABLE students
ADD PRIMARY KEY (student_id);

The student_id column becomes the primary key.

You can add a foreign key after the table has already been created.

ALTER TABLE students
ADD CONSTRAINT fk_course
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

This creates a relationship between the two tables.

A CHECK constraint can be added to an existing table.

ALTER TABLE students
ADD CONSTRAINT chk_age
CHECK (age >= 18);

The database can enforce the specified condition for new or modified data according to its constraint behavior.

Constraints can be removed using ALTER TABLE. The exact syntax depends on the database system and constraint type.

For example, a MySQL foreign key can be removed using:

ALTER TABLE students
DROP FOREIGN KEY fk_course;

The named foreign key constraint is removed.

Constraints help maintain data integrity.

• PRIMARY KEY helps identify records.
• FOREIGN KEY helps maintain relationships.
• UNIQUE helps prevent duplicate values.
• NOT NULL helps prevent missing required values.
• DEFAULT provides automatic values.
• CHECK restricts values according to a condition.

A common mistake is trying to insert duplicate data into a UNIQUE column.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

If the same email is inserted for another record, the database can reject the duplicate value.

Choose constraints according to the purpose of the column.

• Use PRIMARY KEY for unique record identification.
• Use FOREIGN KEY for table relationships.
• Use UNIQUE for values that must not be duplicated.
• Use NOT NULL for required values.
• Use DEFAULT for automatic values.
• Use CHECK for value restrictions.

Here is a practical students table using several constraints.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    status VARCHAR(20) DEFAULT 'Active'
);

This table uses PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT constraints.

A complete example can use constraints across related tables.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    status VARCHAR(20) DEFAULT 'Active',
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

This example demonstrates several important SQL constraints working together.