SQL and DDL
/tldr: The language for defining and managing database structure.
THE CORE IDEA: What is DDL?
SQL (Structured Query Language) is the standard for communicating with relational databases (PostgreSQL, MySQL, SQL Server, etc.). It's divided into four main types of commands.
**DDL** is specifically for creating and modifying the blueprints—the **schema**—that define *how* data is stored.
The Three Pillars of DDL (Define, Change, Remove)
1. CREATE
Constructs new database objects. The most common use is creating tables.
CREATE TABLE table_name ( column_name DATATYPE, ... );
2. ALTER
Modifies the structure of an existing object (e.g., adding a column).
ALTER TABLE table_name ADD COLUMN new_col DATATYPE;
3. DROP
Permanently deletes a database object (e.g., a table, schema, or index).
DROP TABLE table_name; -- Careful! This cannot be undone.
DDL Guardrails: Constraints
Constraints are rules applied to columns to limit the data that can be inserted, ensuring **Data Integrity** and **Data Quality**.
-
PRIMARY KEY
Uniquely identifies every row, must be NOT NULL. The main identifier.
-
FOREIGN KEY
Establishes a link between two tables (Referential Integrity).
-
NOT NULL
Ensures that a column must always contain a value (no missing data).
-
UNIQUE
Ensures all values in a column are different (but can be NULL).
-
CHECK
Ensures all values satisfy a specific condition (e.g., `age > 18`).
-
DEFAULT
Sets a default value for a column when no value is specified.
Data Types TL;DR
Whole Numbers
Variable-length Text
True / False
Exact Decimals
Time data
Minimal DDL Working Example
-- 1. Create a Schema (Namespace)
CREATE SCHEMA IF NOT EXISTS marketing;
-- 2. Create a Table using various types and constraints
CREATE TABLE marketing.customers (
customer_id BIGINT PRIMARY KEY, -- Main unique identifier
first_name VARCHAR(50) NOT NULL, -- Cannot be null
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE, -- Must be unique across all rows
loyalty_points INT DEFAULT 0, -- Set default to 0 if not provided
join_date DATE NOT NULL,
account_status VARCHAR(10) CHECK (account_status IN ('Active', 'Suspended')), -- Enforces specific values
-- Defining a Foreign Key to link to a 'regions' table
region_id INT,
FOREIGN KEY (region_id) REFERENCES marketing.regions(region_id)
);
-- 3. Modify the Table
ALTER TABLE marketing.customers
ADD COLUMN last_login TIMESTAMP;
This single script defines the entire structure for your customer data.
You've mastered the structure.