ASSIGNMENT 39

1. Students table with unique ID

Introduction

Each student must have a unique identifier.

Code

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

Explanation

PRIMARY KEY ensures:
No duplicate IDs
No NULL values
Each student is uniquely identified.

1. Employees table with required name & email

Introduction

Some fields are mandatory, while others can be optional.

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

Explanation

NOT NULL ensures name and email must be provided
phone_number is optional (can be NULL)

1. Users table with unique username & email Introduction

Usernames and emails must be unique for authentication systems.

Code

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

Explanation

UNIQUE prevents duplicate values
Ensures no two users share the same username/email

1. Products table with price and stock constraints

Introduction

Products must have valid pricing and stock values.

Code

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

Explanation

CHECK (price > 0) --> no free/negative products
CHECK (stock >= 0) --> stock cannot be negative

1. Orders table with default status & timestamp

Introduction

Automating values reduces manual errors.

Code

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

Explanation

DEFAULT 'pending' --> automatic order status
CURRENT_TIMESTAMP --> stores insertion time

1. Accounts table with constraints

Introduction

Bank accounts need strict validation rules.

Code

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

Explanation

UNIQUE NOT NULL --> ensures valid account numbers
CHECK (balance >= 0) --> prevents negative balance

1. Enrollments table with composite uniqueness

Introduction

A student can enroll in many courses—but not the same course twice.

Code

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

Explanation

Composite UNIQUE ensures:
No duplicate student-course combinations
Allows multiple courses per student

1. Departments & Employees with foreign key

Introduction

Relationships between tables ensure data consistency.

Code

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

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

Explanation

FOREIGN KEY links employees to departments
Prevents invalid department references

1. Foreign key with CASCADE options

Introduction

Sometimes related data should update/delete automatically.

Code

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

Explanation

ON DELETE CASCADE --> deletes employees when department is deleted
ON UPDATE CASCADE --> updates employee records if department ID changes

Conclusion

Using constraints in table creation:

Improves data integrity
Prevents invalid or duplicate data
Maintains relationships between tables