DDL | Data Definition Language
What Is DDL?
DDL (Data Definition Language) is used to define and manage database structure.
- operates on database objects, not rows
- describes how data is stored
- forms the foundation for all queries and analysis
DDL Scope
DDL affects:
- tables
- columns
- constraints
- indexes
DDL decisions directly impact:
- data quality
- query performance
- scalability
Common DDL Statements
The most common DDL commands are:
CREATEALTERDROPTRUNCATE
Each command changes the database schema or metadata.
CREATE (DDL Context)
In DDL, CREATE is used to define new database objects.
Typical use cases:
- creating schemas
- creating database
- creating tables
- creating indexes
CREATE Example | Index
Example: create an index to improve analytical queries.
CREATE INDEX idx_sales_product_id
ON sales (product_id);
This command:
- does not change data
- improves data access performance
ALTER
ALTER modifies the structure of an existing object.
It allows schema evolution without recreating tables.
ALTER Example | Constraint
Example: enforce a business rule on existing data.
ALTER TABLE products
ADD CONSTRAINT chk_products_price
CHECK (price >= 0);
DROP
DROP permanently removes a database object.
- structure is deleted
- data is lost
- operation is irreversible
DROP Example
Example: remove an unused index.
DROP INDEX idx_sales_product_id;
TRUNCATE
TRUNCATE removes all rows from a table while keeping its structure.
Compared to DELETE:
- much faster
- no
WHERE clause
- limited rollback support
TRUNCATE Example
TRUNCATE TABLE sales_staging;
What Is CRUD?
CRUD represents operations used to work with data inside tables.
CRUD stands for:
- CREATE
- READ
- UPDATE
- DELETE
Critical Terminology Clarification
The word CREATE means different things depending on context.
- DDL CREATE \(\rightarrow\) defines structure
- CRUD CREATE \(\rightarrow\) inserts data
Confusing these is a common beginner mistake.
DDL vs CRUD | Mental Model
- DDL \(\rightarrow\) defines the rules
- CRUD \(\rightarrow\) follows the rules
- DDL \(\rightarrow\) structure
- CRUD \(\rightarrow\) data
CREATE (CRUD \(\rightarrow\) INSERT)
In CRUD, CREATE means adding new rows.
This is done using INSERT.
INSERT INTO products (product_id, product_name, price, category)
VALUES (101, 'Wireless Mouse', 24.99, 'Accessories');
READ (SELECT)
READ retrieves data and is implemented using SELECT.
SELECT
product_id,
product_name
FROM products;
READ with Filtering
Filtering rows is done using WHERE.
SELECT
*
FROM sales
WHERE total_sales < 50;
UPDATE
UPDATE modifies existing records.
Always use WHERE to target specific rows.
UPDATE products
SET price = 49.99
WHERE product_id = 12;
DELETE
DELETE removes records from a table.
Deletions are irreversible and must be used with caution.
DELETE FROM sales
WHERE transaction_id = 1004;
Why Constraints Matter
Constraints play a crucial role in keeping your data organized, consistent, and reliable.
They define what type of data a table or column can accept and are typically applied when tables are created or later via ALTER TABLE.
When defined correctly, constraints:
enforce data integrity
prevent invalid or inconsistent data
act as built-in data quality checks
Types of Constraints
The most common constraints you will encounter are:
UNIQUE
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK
Each constraint enforces a specific business or technical rule.
UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are distinct.
It is commonly used for attributes that must be unique across entities.
Typical use cases include:
email addresses
usernames
national identification numbers
UNIQUE Constraint | Example
Ensure each customer email is unique.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
phone_number TEXT
);
With this constraint in place, PostgreSQL will reject any attempt to insert a duplicate email address.
NOT NULL Constraint
The NOT NULL constraint ensures that a column cannot contain NULL values.
Use this constraint for fields that are mandatory and must always be provided.
Typical use cases include:
primary identifiers
contact information
transaction timestamps
NOT NULL Constraint | Example
Ensure every customer has a phone number.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
phone_number TEXT NOT NULL,
email TEXT
);
If an insert is attempted without a phone number, PostgreSQL will return an error.
PRIMARY KEY Constraint
A PRIMARY KEY uniquely identifies each row in a table.
A primary key:
must be unique
cannot contain NULL values
exists only once per table
PRIMARY KEY Constraint | Example
Define a primary key for a products table.
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
price NUMERIC(10, 2)
);
The PRIMARY KEY constraint automatically enforces both UNIQUE and NOT NULL.
FOREIGN KEY Constraint
A FOREIGN KEY creates a relationship between two tables.
It ensures referential integrity, meaning referenced values must exist in the parent table.
FOREIGN KEY Constraint | Example
Link sales records to customers.
CREATE TABLE sales (
transaction_id INTEGER PRIMARY KEY,
customer_id INTEGER,
total_sales NUMERIC(10, 2),
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);
With this constraint, you cannot insert a sale for a customer that does not exist.
CHECK Constraint
The CHECK constraint restricts the range or condition of values that can be inserted into a column.
It validates data using logical expressions.
Typical use cases include:
value ranges
minimum or maximum limits
domain rules
CHECK Constraint | Example
Ensure total sales values are non-negative.
CREATE TABLE sales (
transaction_id INTEGER PRIMARY KEY,
total_sales NUMERIC(10, 2) CHECK (total_sales >= 0)
);
If a value violates the condition, the insert or update will fail.
SQL Rules and Best Practices
Why Best Practices Matter
Writing SQL that works is not enough.
Good SQL should be:
readable
consistent
maintainable
easy to debug
These rules help teams collaborate and reduce errors.
Numbers and Underscores
In relational databases:
table names cannot start with a number
column names cannot contain spaces
underscores should be used to separate words
This improves compatibility and readability.
Naming | Examples
Recommended:
CREATE TABLE customer_lifecycle;
CREATE TABLE customer;
Not recommended:
CREATE TABLE customer lifecycle;
CREATE TABLE 3customer;
Capitalization
A widely accepted convention is:
SQL keywords \(\rightarrow\) UPPERCASE
table names \(\rightarrow\) lowercase
column names \(\rightarrow\) lowercase
This improves readability and consistency.
Capitalization | Examples
Recommended:
SELECT product_id, product_name
FROM products;
SELECT transaction_id, total_sales
FROM sales;
Not recommended:
Select PRODUCT_ID, PRODUCT_NAME From PRODUCTS;
SELECT TRANSACTION_ID, TOTAL_SALES FROM SALES;
Aliasing
Aliases assign temporary names to tables or columns.
They are useful for:
improving readability
shortening long names
resolving ambiguity in joins
Aliasing | Examples
Column aliases:
SELECT
product_name AS item_name,
price AS unit_price
FROM products;
Table aliases in joins:
SELECT
s.transaction_id,
p.product_name,
s.total_sales
FROM sales AS s
JOIN products AS p
ON s.product_id = p.product_id;
Summary
constraints protect data quality
best practices improve readability
consistent SQL scales better in teams
clean SQL enables better optimization
