• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Constraints

← Previous Next →

In MySQL, constraints are rules applied to table columns to enforce data integrity and accuracy. They ensure that the data stored in tables is valid, consistent, and meaningful.

Constraints are an essential part of database design because they prevent invalid or duplicate data, maintain relationships, and improve reliability. Examples include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

What Are MySQL Constraints?

A constraint is a rule applied to a column or a table that limits the type of data that can be inserted or updated.

Key Points:

  • Ensures data integrity.

  • Can be applied at column level or table level.

  • Helps prevent errors like duplicate entries, invalid values, or orphan records.

  • Crucial for relational database design.

Types of MySQL Constraints

  1. NOT NULL – Ensures a column cannot have NULL values.

  2. UNIQUE – Ensures all values in a column are unique.

  3. PRIMARY KEY – Uniquely identifies each row in a table.

  4. FOREIGN KEY – Enforces referential integrity between tables.

  5. CHECK – Ensures values meet a specific condition.

  6. DEFAULT – Provides a default value for a column if none is specified.

  7. AUTO_INCREMENT – Automatically generates a unique value for new rows.

Column-Level vs Table-Level Constraints

Column-Level Constraints:

  • Applied when creating a column.

  • Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

Table-Level Constraints:

  • Applied after all columns are defined.

  • Useful for multi-column constraints.

  • Example:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

Using Constraints in MySQL

1. NOT NULL Constraint

Ensures a column cannot contain NULL values:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

Explanation:

  • student_name must have a value.

  • Prevents incomplete records.

2. UNIQUE Constraint

Ensures all values in a column are unique:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_code VARCHAR(10) UNIQUE
);

Explanation:

  • No two rows can have the same course_code.

  • Useful for identifiers like email, username, or course codes.

3. PRIMARY KEY Constraint

Uniquely identifies each row:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

Explanation:

  • Combines NOT NULL and UNIQUE.

  • Can be a single column or multiple columns (composite key).

4. FOREIGN KEY Constraint

Enforces referential integrity between tables:

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

Explanation:

  • Ensures student_id exists in the students table.

  • Prevents orphaned records.

5. CHECK Constraint

Ensures values meet a condition:

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

Explanation:

  • Only allows students aged 18 or older.

  • Helps enforce business rules at the database level.

6. DEFAULT Constraint

Provides a default value for a column if none is specified:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    credits INT DEFAULT 3
);

Explanation:

  • If credits is not provided, it automatically defaults to 3.

7. AUTO_INCREMENT Constraint

Automatically generates a unique value for each new row:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50)
);

Explanation:

  • student_id increases automatically.

  • Commonly used for primary keys.

Best Practices for Constraints

  1. Use Constraints Early: Apply constraints during table creation for better data integrity.

  2. Combine Constraints Wisely: Primary keys, foreign keys, and unique constraints work together to ensure accurate data.

  3. Avoid Redundant Constraints: Don’t duplicate rules unnecessarily.

  4. Document Constraints: Maintain a clear reference for your database design.

  5. Test Constraints: Ensure they align with application logic and business requirements.

Common Mistakes to Avoid

  1. Ignoring Constraints: Leads to inconsistent or invalid data.

  2. Incorrect Use of FOREIGN KEY: Can create circular references or errors if referenced tables do not exist.

  3. Not Using UNIQUE or PRIMARY KEY: Can allow duplicate records unintentionally.

  4. Overusing CHECK Constraints: May complicate inserts and updates if rules are too strict.

  5. Neglecting DEFAULT Values: Can cause NULL issues or unexpected results.

Practical Use Cases

  1. Student Database: Enforce NOT NULL for names, PRIMARY KEY for student ID, and FOREIGN KEY for enrollments.

  2. E-commerce: Ensure unique product codes, auto-increment order IDs, and default values for stock quantity.

  3. Employee Management: Enforce unique email addresses, not null columns for critical information, and check constraints on salary.

  4. Blog Application: Unique slugs for posts, foreign keys for comments referencing posts, and default status for new posts.

  5. Inventory System: Constraints ensure accurate stock levels, valid categories, and unique item IDs.

Summary of the Tutorial

MySQL Constraints are vital for maintaining data integrity, accuracy, and reliability.

  • Use NOT NULL to prevent missing values.

  • Use UNIQUE and PRIMARY KEY to enforce uniqueness.

  • Use FOREIGN KEY to maintain relational integrity.

  • Use CHECK to validate values.

  • Use DEFAULT to provide fallback values.

  • Use AUTO_INCREMENT for automatically generated identifiers.

Proper understanding and application of constraints ensures robust, clean, and consistent databases.

Practice Questions

  1. Create a table students with student_id as PRIMARY KEY, student_name as NOT NULL, and email as UNIQUE.

  2. Add a CHECK constraint on the age column of the students table to allow only ages greater than or equal to 18.

  3. Create a table courses with course_id as AUTO_INCREMENT PRIMARY KEY and credits with a DEFAULT value of 3.

  4. Add a FOREIGN KEY constraint on the enrollments table linking student_id to students(student_id).

  5. Create a table employees with employee_id as PRIMARY KEY, email as UNIQUE, and salary as NOT NULL.

  6. Add a DEFAULT value of 'active' to a column status in the users table.

  7. Drop a CHECK constraint from the students table that restricts age.

  8. Create a table departments with department_id as PRIMARY KEY and department_name as UNIQUE.

  9. Add an AUTO_INCREMENT column order_id to an existing table orders.

  10. Create a table products with product_id as PRIMARY KEY, product_code as UNIQUE, price as NOT NULL, and stock with a DEFAULT value of 0.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top