Create Tables

Students Table

Every student should have a unique identifier. This is achieved using a primary key.

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

In many real-world cases, certain fields must always have values. For employees, name and email are required, while phone number can be optional.

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

To prevent duplicate accounts, both username and email should be unique.

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

Products Table
Constraints can also enforce logical rules. A product cannot have a negative stock or a price less than or equal to zero.

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

Orders Table
Defaults make data handling easier. If no status is provided, it automatically becomes "pending". The creation time is also stored automatically.

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

Accounts Table
Here we combine multiple constraints. Account numbers must be unique and cannot be null, while balance should never go below zero.

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

Enrollments Table
A student can enroll in multiple courses, but cannot enroll in the same course more than once. This is handled using a composite unique constraint.

CREATE TABLE enrollments (id SERIAL PRIMARY KEY,student_id INT,course_id INT,UNIQUE (student_id, course_id));
Departments and Employees Relationship

To connect employees with departments, we use a foreign key. This ensures that every employee belongs to a valid department.

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));
Cascading Changes

Sometimes, changes in one table should reflect in another. If a department is deleted, its employees should also be removed. Similarly, updates to department IDs should propagate.

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);

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

Conclusion

SQL constraints play a crucial role in maintaining data accuracy and reliability. By using primary keys, unique constraints, checks, defaults, and foreign keys, we can design robust database systems that prevent invalid data and enforce meaningful relationships between tables.