postgresql

Introduction to DDL (Data Definition Language) in SQL
When we talk about databases, we usually think of inserting, updating, or deleting data. But before doing any of that, we must first define the structure the blueprint of how data will be stored.

That’s where DDL (Data Definition Language) comes in.

It is a subset of SQL used to define, modify, or remove database structures such as tables, schemas, indexes, and views

CREATE TABLE customers ( 
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK ( age >= 18 ),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);


ALTER TABLE customers ADD COLUMN phone VARCHAR(15);

DROP TABLE customers;

TRUNCATE TABLE customers;

The CREATE TABLE Command

The most frequently used DDL command is CREATE TABLE.
It defines the structure of a table the columns, their data types, and constraints.

Adding Constraints

Constraints help maintain data integrity and define rules for your data.

Let’s understand all of them with examples.

PRIMARY KEY

A PRIMARY KEY uniquely identifies each record in the table and doesn’t allow NULL values.

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100),
    salary NUMERIC(10,2),
    joining_date DATE
);

NOT NULL

Ensures a column cannot have NULL values.

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    salary NUMERIC(10,2),
    joining_date DATE NOT NULL
);

UNIQUE

Ensures all values in a column are different (can allow a single NULL).

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    emp_name VARCHAR(100) NOT NULL
);

CHECK

Adds a condition that must be satisfied for every row.

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    salary NUMERIC(10,2) CHECK (salary > 0),
    joining_date DATE
);

DEFAULT

Assigns a default value if no value is provided.

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
salary NUMERIC(10,2) DEFAULT 30000,
joining_date DATE DEFAULT CURRENT_DATE
);

If salary or joining_date isn’t given, PostgreSQL fills them automatically.

FOREIGN KEY

Creates a link between two tables ensuring referential integrity.

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT REFERENCES departments(dept_id) ON DELETE CASCADE
);