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.

What is DDL?

DDL stands for Data Definition Language.
It is a subset of SQL used to define, modify, or remove database structures such as tables, schemas, indexes, and views.

In short DDL defines the structure of your database.

Command	Description
CREATE	Used to create database objects like tables, views, or schemas
ALTER	Used to modify the structure of existing objects
DROP	Used to delete database objects
TRUNCATE	Used to remove all rows from a table instantly
RENAME	Used to rename database objects

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.

Example 1: Basic Table Creation

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

Here we created an employees table with 4 columns.
But there are no rules yet meaning duplicates or NULLs are allowed.

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

If anyone tries to insert a negative salary ❌ the database will reject it.

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

dept_id in employees refers to departments(dept_id)

2. ON DELETE CASCADE ensures if a department is deleted, all its employees are deleted too.

Example 2: Combining All Constraints

Let’s bring everything together.

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary NUMERIC(10,2) CHECK (salary > 0),
    dept_id INT REFERENCES departments(dept_id) ON DELETE SET NULL,
    joining_date DATE DEFAULT CURRENT_DATE
);

Modifying the Table (ALTER)

Once created, you can change your table using ALTER TABLE.

Operation	Example
Add a column	`ALTER TABLE employees ADD COLUMN phone VARCHAR(15);`
Modify data type	`ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);`
Drop a column	`ALTER TABLE employees DROP COLUMN phone;`
Add constraint	`ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE(email);`

Understanding DDL and mastering CREATE TABLE with constraints is the foundation of database design.

A well-defined schema ensures data consistency, integrity, and performance from the very beginning.

Parottasalna