CA 39 - Create Tables

#beginners #database #devjournal #sql

Today I practiced some basic SQL constraints. I kept it simple and tried different rules like primary key, unique, not null, check, and foreign key.

1. Students Table (Unique ID)

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

id is unique for every student

2. Employees Table (No Empty Name & Email)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15)
);

name & email must be filled
phone is optional

3. Users Table (Unique Username & Email)

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

no duplicates allowed

4. Products Table (Valid Price & Stock)

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price INT CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

price > 0
stock ≥ 0

5. Orders Table (Default + Timestamp)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

status auto = pending
time auto added

6. Accounts Table (Safe Balance)

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance INT CHECK (balance >= 0)
);

no duplicate account numbers
no negative balance

7. Enrollments Table (No Duplicate Entry)

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

same student can't join same course twice

8. Foreign Key (Departments & Employees)
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

employee must belong to a valid department

9. Auto Delete & Update (Advanced FK)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);