Understanding SQL Table Creation with Constraints Introduction

_In this task, I created different tables in SQL by applying various constraints. While writing these queries, I focused on understanding why each constraint is used and how it helps in maintaining proper data in the database.

_

1. Students Table

Query:

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

Explanation:

In this table, I created three columns: id, name, and age. The id is defined as a primary key, which means each record will have a unique identifier. I used SERIAL so that the id is automatically generated. This avoids duplication and makes it easier to identify each student.

1. Employees Table

Query:

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

Explanation:

In this table, name and email are marked as NOT NULL, which means these fields cannot be left empty. The phone_number is optional, so it can have null values. This ensures that important details are always stored.

1. Users Table

Query:

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

Explanation:

Here, I used UNIQUE for both username and email. This ensures that no two users can have the same username or email. It helps prevent duplicate entries and maintains consistency.

1. Products Table

Query:

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

Explanation:

In this table, I used CHECK constraints to control the values. The price must be greater than zero, and stock must be zero or more. This prevents invalid data like negative stock or incorrect pricing.

1. Orders Table

Query:

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

Explanation:

In this table, I used default values. If no status is given, it will automatically be set to 'pending'. The created_at column stores the current timestamp, which helps in tracking when the order was created.

1. Accounts Table

Query:

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

Explanation:

Here, account_number is both UNIQUE and NOT NULL, so every account must have a unique value and it cannot be empty. The balance is restricted using a CHECK condition to ensure it is not negative.

1. Enrollments Table

Query:

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

Explanation:

In this table, I used a combination of student_id and course_id as UNIQUE. This means a student cannot enroll in the same course more than once, but can enroll in different courses.

1. Departments and Employees (Foreign Key)

Query:

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

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

Explanation:

Here, I created two tables. The employees table has a foreign key called department_id, which refers to the id in the departments table. This ensures that every employee is linked to a valid department.

1. Foreign Key with Cascade

Query:

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

CREATE TABLE employees_cascade (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments_cascade(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

Explanation:

In this case, I added cascade options. When a department is deleted, all related employees are also deleted automatically. When a department id is updated, the change is reflected in the employees table. This helps maintain consistency without manual updates.

I conclude with :

From this task, I understood that constraints play an important role in maintaining correct and meaningful data. Each constraint serves a specific purpose, such as avoiding duplicates, preventing invalid values, and maintaining relationships between tables.