Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Constraints
VideosDiscussion
In relational databases, storing data is not enough.
It is equally important to ensure that the data stored is valid, consistent, and meaningful.

SQL provides Constraints and Keys to enforce rules on tables and columns.
These rules act as safeguards that prevent incorrect or illogical data from entering the database.

In this chapter, we take a deeper look at:

  • CHECK Constraint
  • DEFAULT Constraint
  • Composite Keys
Each of these plays a critical role in data validation and table design.


Why a Deeper Understanding Is Important

At a beginner level, constraints may appear simple.
However, in real-world applications:

  • Data must follow business rules
  • Columns may depend on multiple conditions
  • Uniqueness may require more than one column
Understanding these advanced uses helps in designing robust and scalable databases.


1. CHECK Constraint

The CHECK constraint ensures that values in a column satisfy a specific condition.
If the condition evaluates to false, the database rejects the operation.


Why Use CHECK?

CHECK is used to:

  • Enforce business rules
  • Prevent invalid ranges or values
  • Improve data quality at the database level


Example: Column Level CHECK

CREATE TABLE students (
    student_id INT,
    marks INT CHECK (marks >= 0 AND marks <= 100)
);

This ensures that marks can only be between 0 and 100.


Example: Table Level CHECK

CREATE TABLE employees (
    emp_id INT,
    age INT,
    salary INT,
    CHECK (age >= 18 AND salary > 0)
);


2. DEFAULT Constraint

The DEFAULT constraint assigns a predefined value to a column when no value is provided during insertion.


Why Use DEFAULT?

DEFAULT is useful when:

  • A column usually has a common value
  • You want to avoid NULL entries
  • You want consistent initial data


Example

CREATE TABLE employees (
    emp_id INT,
    status VARCHAR(20) DEFAULT 'Active',
    joining_date DATE DEFAULT CURRENT_DATE
);

If no value is given, the database automatically assigns the default value.


3. Composite Keys

A Composite Key is a key formed using two or more columns to uniquely identify a record.
It is used when no single column is sufficient to ensure uniqueness.


Why Use Composite Keys?

Composite keys are commonly used when:

  • Multiple columns together define uniqueness
  • Many-to-many relationships exist
  • Data combinations must be unique


Example

CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

Here, the combination of student_id and course_id uniquely identifies each enrollment.


Two Minute Drill

  • Constraints enforce data rules
  • CHECK validates values using conditions
  • DEFAULT assigns automatic values
  • Composite Keys use multiple columns
  • Helps maintain data integrity
  • Essential for real world database design