Create Tables

Today I learned how to create tables in SQL and how to apply different constraints. At first, it felt confusing, but when I understood the purpose behind each rule, it became much easier.

Let me explain each task in a simple way.

1. Creating a Students Table First, I created a table called students. Each student should have an id, name, and age. The important thing here is that id must be unique, so we use a PRIMARY KEY.

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
Here, PRIMARY KEY ensures that no two students will have the same id.

1. Employees Table with Required Fields Next, I created an employees table. In this case, name and email should not be empty, but phone number is optional.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
phone_number VARCHAR(15)
);
NOT NULL means the user must provide a value.

1. Users Table with Unique Values In this task, both username and email must be unique. This is useful in real systems like login systems.

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
Here, UNIQUE prevents duplicate entries.

1. Products Table with Conditions For products, I learned how to restrict values using CHECK constraints.

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price > 0),
stock INT CHECK (stock >= 0)
);
This ensures:

• Price is always positive
• Stock is never negative

1. Orders Table with Default Values Sometimes, we want default values. For example, if no status is given, it should be pending.

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This automatically sets:

• status → pending
• created_at → current time
• Accounts Table with Rules In banking-like systems, we must be very careful with data.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
account_number VARCHAR(20) UNIQUE NOT NULL,
balance DECIMAL(10,2) CHECK (balance >= 0)
);
This ensures:

• account number is always unique
• balance never goes below zero
• Enrollments Table (Combination Unique) Here I learned something interesting. A student can take many courses, but the same student cannot enroll in the same course twice.

CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
This is called a composite key.

1. Foreign Key Relationship (Departments & Employees) Now I created two tables where one depends on another.

CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

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

• This ensures that an employee can only belong to an existing department.
• Cascade Delete and Update Finally, I improved the previous example by adding automatic updates.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
This means:

• If a department is deleted → its employees are also deleted
• If department id changes → it updates automatically