CA 39 – Create Tables

This exercise was about creating tables with different constraints like primary key, unique, not null, check, default and foreign key. I wrote the queries and tried to understand why we use each constraint.

1. Create students table with id unique

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

Here id is primary key so it will be unique automatically and identify each student.

1. Create employees where name and email cannot be empty

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

phone_number is optional so no NOT NULL.

1. Users table where username and email must be unique

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

UNIQUE makes sure no duplicate username or email.

1. Products table where price > 0 and stock not negative

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

CHECK constraint used for conditions.

1. Orders table with default status and timestamp

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

DEFAULT automatically inserts value.

1. Accounts table

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

1. Enrollments table unique combination

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

This prevents same student enrolling same course twice.

1. Departments and Employees with 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)
);

Foreign key ensures department must exist.

1. Foreign key with delete and update 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
);

If department deleted, employees also deleted. If department id updated, it updates here also.

From this exercise I understood constraints are very important because they prevent wrong data from entering into database and maintain consistency.