SQL Integrity Constraints – NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY

SQL Integrity Constraints – Tutorial

Integrity Constraints are rules applied to table columns to ensure data accuracy, consistency, and validity in a database. They prevent invalid or duplicate data from being entered into the table.

1. NOT NULL

Definition: Ensures a column cannot have NULL values.

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50)
);

Here, name must have a value; it cannot be empty.

2. UNIQUE

Definition: Ensures all values in a column are distinct.

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    email VARCHAR(50) UNIQUE
);

No two students can have the same email address.

3. CHECK

Definition: Ensures a column satisfies a specific condition.

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

Age must be 18 or above.

4. PRIMARY KEY

Definition: Uniquely identifies each record in a table. Cannot have NULL values.

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);

student_id uniquely identifies each student.

5. FOREIGN KEY

Definition: Ensures a column references the primary key of another table for referential integrity.

CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

The dept_id in Student table must match a valid dept_id in Department table.

Lab Practice Tasks

  1. Create a Student table using NOT NULL, UNIQUE, and CHECK constraints.
  2. Create a Department table and reference it using FOREIGN KEY in Student table.
  3. Insert valid and invalid data to see constraints in action.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)