Data Definition Language (DDL) creates and modifies database structure. Good schema design with proper constraints, indexes, and data types is fundamental to database performance and data integrity.
| Statement / Feature | Syntax | Description |
|---|---|---|
| CREATE TABLE | CREATE TABLE name (col type constraints, ...) | Create new table |
| CREATE TABLE IF NOT EXISTS | CREATE TABLE IF NOT EXISTS name (...) | Create only if not exists |
| DROP TABLE | DROP TABLE tablename | Delete table and all data — irreversible! |
| DROP TABLE IF EXISTS | DROP TABLE IF EXISTS tablename | Drop only if exists — no error if missing |
| ALTER TABLE ADD | ALTER TABLE t ADD COLUMN col type | Add new column |
| ALTER TABLE DROP | ALTER TABLE t DROP COLUMN col | Remove column |
| ALTER TABLE MODIFY | ALTER TABLE t MODIFY col newtype / ALTER COLUMN | Change column type |
| ALTER TABLE RENAME | ALTER TABLE old RENAME TO new | Rename table |
| RENAME COLUMN | ALTER TABLE t RENAME COLUMN old TO new | Rename column (PostgreSQL/SQLite) |
| PRIMARY KEY | col INTEGER PRIMARY KEY / PRIMARY KEY (col1,col2) | Unique non-null identifier |
| FOREIGN KEY | FOREIGN KEY (col) REFERENCES other(col) ON DELETE CASCADE | Referential integrity |
| UNIQUE | col VARCHAR(100) UNIQUE / UNIQUE (col1,col2) | Enforce uniqueness |
| NOT NULL | col VARCHAR(100) NOT NULL | Disallow null values |
| DEFAULT | col INTEGER DEFAULT 0 / DEFAULT CURRENT_TIMESTAMP | Default value when not specified |
| CHECK | salary DECIMAL CHECK (salary > 0) / CHECK (status IN ('a','b')) | Value constraint |
| AUTO_INCREMENT | id INT AUTO_INCREMENT PRIMARY KEY | MySQL auto-increment primary key |
| SERIAL | id SERIAL PRIMARY KEY | PostgreSQL auto-increment |
| IDENTITY | id INT GENERATED ALWAYS AS IDENTITY | SQL standard / PostgreSQL 10+ / SQL Server |
| CREATE INDEX | CREATE INDEX idx_name ON table(col) | Speed up queries on column |
| CREATE UNIQUE INDEX | CREATE UNIQUE INDEX idx ON table(col) | Index + uniqueness constraint |
| CREATE INDEX multi | CREATE INDEX idx ON table(col1, col2) | Composite index |
| DROP INDEX | DROP INDEX idx_name | Remove index |
| CREATE VIEW | CREATE VIEW vname AS SELECT ... | Named virtual table from query |
| CREATE OR REPLACE VIEW | CREATE OR REPLACE VIEW vname AS SELECT ... | Create or update view |
| DROP VIEW | DROP VIEW viewname | Remove view |
| VARCHAR | VARCHAR(255) | Variable-length string up to n chars |
| TEXT | TEXT | Unlimited-length string (MySQL/PG) |
| INTEGER / INT | INTEGER | 32-bit integer |
| BIGINT | BIGINT | 64-bit integer |
| DECIMAL | DECIMAL(10,2) | Exact numeric with precision and scale |
| FLOAT / REAL | FLOAT / REAL | Approximate floating point |
| BOOLEAN | BOOLEAN / BOOL | True/false (MySQL: TINYINT(1)) |
| DATE | DATE | Date only: YYYY-MM-DD |
| TIMESTAMP | TIMESTAMP / TIMESTAMP WITH TIME ZONE | Date + time |
| JSON | JSON / JSONB | JSON data type (PostgreSQL JSONB = binary, indexed) |
| ENUM | ENUM('val1','val2') | Fixed set of allowed values (MySQL) |
-- DDL — Schema Design Best Practices
-- ── Create tables with full constraints ──────────────────────
CREATE TABLE universities (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
domain VARCHAR(100) NOT NULL UNIQUE,
founded INTEGER CHECK (founded BETWEEN 1000 AND 2100),
country CHAR(2) NOT NULL DEFAULT 'US',
active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
univ_id INTEGER NOT NULL REFERENCES universities(id) ON DELETE RESTRICT,
code VARCHAR(20) NOT NULL,
title VARCHAR(300) NOT NULL,
credits SMALLINT NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 12),
level VARCHAR(20) NOT NULL DEFAULT 'beginner'
CHECK (level IN ('beginner','intermediate','advanced')),
price DECIMAL(8,2) NOT NULL DEFAULT 0.00 CHECK (price >= 0),
published BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE (univ_id, code) -- course codes unique per university
);
CREATE TABLE enrollments (
id SERIAL PRIMARY KEY,
course_id INTEGER NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
student_email VARCHAR(150) NOT NULL,
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP,
score DECIMAL(5,2) CHECK (score IS NULL OR (score BETWEEN 0 AND 100)),
UNIQUE (course_id, student_email)
);
-- ── Indexes ──────────────────────────────────────────────────
CREATE INDEX idx_courses_univ ON courses(univ_id);
CREATE INDEX idx_courses_level ON courses(level) WHERE published = TRUE;
CREATE INDEX idx_enrollments_student ON enrollments(student_email);
CREATE INDEX idx_enrollments_course ON enrollments(course_id, enrolled_at DESC);
-- ── Views ────────────────────────────────────────────────────
CREATE OR REPLACE VIEW course_stats AS
SELECT
c.id, c.code, c.title, c.credits,
u.name AS university,
COUNT(e.id) AS enrollments,
AVG(e.score) AS avg_score,
SUM(CASE WHEN e.completed_at IS NOT NULL THEN 1 ELSE 0 END) AS completions
FROM courses c
JOIN universities u ON c.univ_id = u.id
LEFT JOIN enrollments e ON c.id = e.course_id
WHERE c.published = TRUE
GROUP BY c.id, c.code, c.title, c.credits, u.name;
-- Usage
SELECT * FROM course_stats ORDER BY enrollments DESC LIMIT 10;
-- ── ALTER TABLE examples ─────────────────────────────────────
ALTER TABLE courses ADD COLUMN tags TEXT[]; -- PostgreSQL array
ALTER TABLE courses ADD COLUMN description TEXT;
ALTER TABLE courses ALTER COLUMN price SET DEFAULT 0.00;
ALTER TABLE enrollments ADD COLUMN grade CHAR(2);
CREATE INDEX idx_courses_tags ON courses USING GIN(tags); -- PostgreSQL GIN index