Creating Tables using constraints

In this assignment, I practiced creating tables with different constraints like PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT and FOREIGN KEY.

1.Students Table (Unique ID)

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT
);

2.Employees Table (NOT NULL fields)

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone_number TEXT
);

3.Users Table (Unique username & email)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT UNIQUE
);

4.Products Table (CHECK constraints)

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

5.Orders Table (DEFAULT values)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6.Accounts Table (UNIQUE + CHECK)

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

7.Enrollments Table (Composite UNIQUE)

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

8.Departments & Employees (Foreign Key)

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

9.Foreign Key with CASCADE

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