Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

DLL Key Constraints

Key constraints are part of Data Definition Language (DDL) and are used to enforce rules on data columns to maintain accuracy, uniqueness, and relationships in a database.

🧱 PRIMARY KEY

Ensures each row has a unique, non-null identifier.

📌 Example:

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

✔️ id must be unique and not null

✔️ Only one primary key per table

✔️ Can also be composite (multiple columns)

🔗 FOREIGN KEY

Creates a relationship between two tables.

A FOREIGN KEY in one table references a PRIMARY KEY in another.

📌 Example:

CREATE TABLE enrollments (
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id)
);

✔️ Ensures that student_id exists in students

🔑 UNIQUE

Ensures all values in a column are different

Unlike PRIMARY KEY, allows nulls and multiple unique keys in a table

📌 Example:

CREATE TABLE users (
  email VARCHAR(100) UNIQUE
);

✔️ Two users can't have the same email

🚫 NOT NULL

Ensures a column must always contain a value

📌 Example:

CREATE TABLE employees (
  name VARCHAR(50) NOT NULL
);

✔️ Name cannot be empty

🧮 CHECK

Used to enforce custom conditions

📌 Example:

CREATE TABLE products (
  price DECIMAL(10, 2) CHECK (price > 0)
);

✔️ Prevents negative prices

🔄 DEFAULT

Automatically assigns a value if none is provided

📌 Example:

CREATE TABLE orders (
  status VARCHAR(20) DEFAULT 'Pending'
);

✔️ If status is not entered, it will be 'Pending'

📋 Summary Table

Constraint

Purpose

🔐 PRIMARY KEY

Unique + Not Null, identifies each row

🔗 FOREIGN KEY

Links to primary key in another table

🔑 UNIQUE

Ensures all values in a column are distinct

🚫 NOT NULL

Prevents missing/empty values

🧮 CHECK

Validates column values with condition

🔄 DEFAULT

Auto-fills column with default value