HintsToday

Here’s a clear explanation of SQL Keys—including PRIMARY KEY, UNIQUE, FOREIGN KEY, and others—with examples to help you understand their purpose, constraints, and usage in real-world tables.

🔑 SQL KEYS – Concept and Purpose

SQL keys are constraints used to:

• Enforce data integrity
• Prevent duplicate or invalid data
• Maintain relationships between tables

1️⃣ PRIMARY KEY

• Ensures uniqueness + non-null
• Only one per table
• Can be single or composite

✅ Example:

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT
);

• emp_id must be unique and NOT NULL.

🧠 Composite Primary Key:

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

2️⃣ UNIQUE Key

• Ensures unique values in a column (like Primary Key)
• Allows one NULL (depends on DBMS)
• Can have multiple UNIQUE keys per table

✅ Example:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  username VARCHAR(50) UNIQUE
);

• Both email and username must be unique.

3️⃣ FOREIGN KEY

• Creates a link between two tables
• References a PRIMARY or UNIQUE key in another table
• Enforces referential integrity

✅ Example:

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

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

• employees.dept_id must exist in departments.dept_id

4️⃣ NOT NULL

• Column must have a value
• Often used with primary/unique keys

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL
);

5️⃣ CHECK

• Enforces a condition
• Used to restrict values in a column

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  balance DECIMAL(10,2),
  CHECK (balance >= 0)
);

6️⃣ DEFAULT

• Sets default value if none provided

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending'
);

🎯 Real-world Schema Example (with Keys)

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE NOT NULL,
  amount DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

🔍 Key Differences Summary

🧪 Test Your Understanding

❓Can a table have:

• Multiple unique keys? → ✅ Yes
• Multiple foreign keys? → ✅ Yes
• Multiple primary keys? → ❌ No
• NULL in a unique key? → ✅ Yes (only once, depends on DB)

Pages: 1 2 3 4