CREATE, ALTER & DROP (DDL)

Data Definition Language (DDL)

DDL statements define the structure of a database — tables, columns, indexes, views, and more. Unlike DML (INSERT, UPDATE, DELETE), DDL changes the schema, not the data.

Statement	Action
CREATE	Creates a new database object (table, index, view, etc.)
ALTER	Modifies an existing object
DROP	Permanently deletes an object and its data
TRUNCATE	Removes all rows from a table (fast; preserves structure)
RENAME	Renames an object

DDL is auto-committed. In most databases, DDL statements are committed immediately — you usually cannot roll them back. DROP TABLE is permanent.

CREATE TABLE

Basic Syntax

CREATE TABLE table_name (
    column1  datatype  constraints,
    column2  datatype  constraints,
    ...
    table_constraint
);

Example: Full Employee Table

CREATE TABLE employees (
    id            INTEGER      PRIMARY KEY AUTOINCREMENT,
    name          VARCHAR(100) NOT NULL,
    department    VARCHAR(50)  NOT NULL,
    salary        DECIMAL(10,2) NOT NULL DEFAULT 0,
    hire_date     DATE         NOT NULL,
    email         VARCHAR(150) UNIQUE,
    is_active     BOOLEAN      DEFAULT TRUE,
    manager_id    INTEGER,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE IF NOT EXISTS

Prevents an error if the table already exists:

CREATE TABLE IF NOT EXISTS employees (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL
);

Use this in scripts that might be run multiple times.

Data Types

Different databases have slightly different type names, but the categories are universal:

Integer Types

id     INTEGER         -- Flexible integer
count  SMALLINT        -- Small integer (up to 32,767)
amount BIGINT          -- Large integer (billions+)

Decimal / Float Types

salary   DECIMAL(10, 2)  -- Up to 10 digits total, 2 after decimal (exact)
rate     NUMERIC(5, 4)   -- 5 total digits, 4 decimal places (exact)
score    FLOAT           -- Approximate (avoid for financial data)
ratio    REAL            -- Approximate 32-bit float

For monetary values, always use DECIMAL or NUMERIC — never FLOAT or REAL (which have rounding errors).

Text Types

name    VARCHAR(100)  -- Variable-length string, max 100 chars
code    CHAR(3)       -- Fixed-length string, always 3 chars (padded)
bio     TEXT          -- Unlimited-length string

Date/Time Types

hire_date   DATE       -- '2026-06-27'
event_time  TIME       -- '14:30:00'
created_at  TIMESTAMP  -- '2026-06-27 14:30:00'

Boolean

is_active  BOOLEAN  -- TRUE / FALSE
-- SQLite stores as INTEGER (1/0); use TINYINT or INTEGER in SQLite

Column Constraints

Constraints enforce rules at the column level:

CREATE TABLE products (
    id          INTEGER      PRIMARY KEY,           -- Unique, not null
    sku         VARCHAR(20)  UNIQUE NOT NULL,       -- Unique & required
    name        VARCHAR(100) NOT NULL,              -- Required
    price       DECIMAL(8,2) NOT NULL CHECK (price >= 0),  -- Must be non-negative
    category    VARCHAR(50)  DEFAULT 'General',    -- Default value
    in_stock    BOOLEAN      DEFAULT TRUE,
    description TEXT                                -- Optional (allows NULL)
);

Constraint	Effect
PRIMARY KEY	Unique + NOT NULL; identifies each row
NOT NULL	Column cannot be empty
UNIQUE	No two rows can have the same value
DEFAULT val	Value used if none is provided
CHECK (expr)	Row is rejected if expression is FALSE
FOREIGN KEY	Value must exist in another table

Table-Level Constraints

Some constraints apply to multiple columns and must be defined at the table level:

CREATE TABLE order_items (
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL DEFAULT 1,
    unit_price  DECIMAL(8,2) NOT NULL,

    PRIMARY KEY (order_id, product_id),     -- Composite primary key
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    CHECK (quantity > 0)
);

ON DELETE / ON UPDATE Actions

When the parent row is deleted or its key updated, what should happen to child rows?

Action	Behaviour
RESTRICT	Reject the delete/update if children exist (default)
CASCADE	Automatically delete/update child rows
SET NULL	Set the foreign key column to NULL
SET DEFAULT	Set to the column's default value

CREATE TABLE AS … SELECT

Create a new table from the result of a query:

-- Create a finance_employees table from existing data
CREATE TABLE finance_employees AS
SELECT id, name, salary, hire_date
FROM employees
WHERE department = 'Finance';

This copies both structure and data. Constraints are not copied.

ALTER TABLE

ALTER TABLE modifies an existing table without losing its data.

Add a Column

ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees ADD COLUMN performance_score DECIMAL(3,1) DEFAULT 0;

New columns are initially NULL (or the default) for all existing rows.

Drop a Column

ALTER TABLE employees DROP COLUMN phone;

-- SQLite doesn't support DROP COLUMN before version 3.35

Rename a Column

-- PostgreSQL / MySQL 8+
ALTER TABLE employees RENAME COLUMN department TO dept;

-- SQLite — requires recreating the table (or use SQLite 3.25+)
ALTER TABLE employees RENAME COLUMN department TO dept;  -- SQLite 3.25+

Rename a Table

ALTER TABLE employees RENAME TO staff;
-- Or
RENAME TABLE employees TO staff;   -- MySQL

Modify a Column's Data Type or Constraints

-- MySQL
ALTER TABLE employees MODIFY salary DECIMAL(12, 2) NOT NULL;

-- PostgreSQL
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12, 2);
ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;
ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 50000;
ALTER TABLE employees ALTER COLUMN salary DROP NOT NULL;

Add a Constraint

-- Add a UNIQUE constraint on email
ALTER TABLE employees ADD CONSTRAINT uq_email UNIQUE (email);

-- Add a CHECK constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

-- Add a foreign key
ALTER TABLE employees
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES employees(id);

Drop a Constraint

ALTER TABLE employees DROP CONSTRAINT uq_email;
ALTER TABLE employees DROP CONSTRAINT chk_salary;
-- MySQL:
ALTER TABLE employees DROP FOREIGN KEY fk_manager;

DROP TABLE

Permanently removes the table and all its data. This cannot be undone.

DROP TABLE employees;

-- Safe version — no error if table doesn't exist
DROP TABLE IF EXISTS employees;

Drop Multiple Tables

DROP TABLE IF EXISTS order_items, orders, products, customers;

Drop tables in the right order — child tables (with foreign keys) before parent tables.

DROP DATABASE

Removes an entire database and all objects within it:

DROP DATABASE IF EXISTS company_db;  -- EXTREME CAUTION

TRUNCATE TABLE

Removes all rows instantly; keeps the table structure:

TRUNCATE TABLE temp_calculations;

Faster than DELETE FROM table because it doesn't log each row deletion. The auto-increment counter is usually reset.

CREATE SCHEMA / Database

Organise tables into schemas (namespaces):

-- PostgreSQL
CREATE SCHEMA hr;
CREATE TABLE hr.employees (...);
SELECT * FROM hr.employees;

-- MySQL — schema = database
CREATE DATABASE company;
USE company;
CREATE TABLE employees (...);

Practical Examples

Example 1: Create a Full Company Database Schema

CREATE TABLE departments (
    id      INTEGER      PRIMARY KEY,
    name    VARCHAR(50)  NOT NULL UNIQUE,
    budget  DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE employees (
    id            INTEGER       PRIMARY KEY AUTOINCREMENT,
    name          VARCHAR(100)  NOT NULL,
    email         VARCHAR(150)  UNIQUE,
    department_id INTEGER       NOT NULL,
    salary        DECIMAL(10,2) NOT NULL,
    hire_date     DATE          NOT NULL,
    manager_id    INTEGER,

    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id)    REFERENCES employees(id)
);

CREATE TABLE projects (
    id            INTEGER       PRIMARY KEY AUTOINCREMENT,
    title         VARCHAR(200)  NOT NULL,
    department_id INTEGER,
    budget        DECIMAL(12,2),
    start_date    DATE,
    end_date      DATE,

    FOREIGN KEY (department_id) REFERENCES departments(id),
    CHECK (end_date IS NULL OR end_date > start_date)
);

Example 2: Evolving a Table Over Time

-- Initial table
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

-- Later: add phone and loyalty tier
ALTER TABLE customers ADD COLUMN phone    VARCHAR(20);
ALTER TABLE customers ADD COLUMN tier     VARCHAR(20) DEFAULT 'Bronze';
ALTER TABLE customers ADD COLUMN joined   DATE;

-- Rename a column
ALTER TABLE customers RENAME COLUMN name TO full_name;

-- Add a constraint
ALTER TABLE customers ADD CONSTRAINT chk_tier
CHECK (tier IN ('Bronze', 'Silver', 'Gold', 'Platinum'));

Common Mistakes

1. Dropping a referenced table

-- If orders.customer_id references customers.id:
DROP TABLE customers;  -- Error: foreign key constraint violation

-- Must drop child table first
DROP TABLE orders;
DROP TABLE customers;
-- Or use CASCADE (drops related constraints automatically — PostgreSQL)
DROP TABLE customers CASCADE;

2. Using FLOAT for money

-- Wrong — floating-point arithmetic errors
price FLOAT

-- Correct — exact decimal arithmetic
price DECIMAL(10, 2)

3. Forgetting IF NOT EXISTS / IF EXISTS

-- Error if table already exists
CREATE TABLE employees (...);

-- Safe
CREATE TABLE IF NOT EXISTS employees (...);
DROP TABLE IF EXISTS old_temp_table;

Practice Exercises

1. Create a customers table with columns: id (primary key, auto-increment), name (required), email (unique), registered_date, and loyalty_points (default 0).
2. Add a phone_number column to the customers table.
3. Create a products table with a CHECK constraint ensuring price is greater than zero.
4. Write a CREATE TABLE AS SELECT query to create a high_earners table with employees earning above 80,000.
5. Write the commands to safely drop the high_earners table.

Summary

In this chapter you learned:

• CREATE TABLE — define columns, data types, and constraints
• Common data types: INTEGER, DECIMAL(p,s), VARCHAR(n), TEXT, DATE, TIMESTAMP, BOOLEAN
• Column constraints: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, FOREIGN KEY
• CREATE TABLE IF NOT EXISTS — safe creation in scripts
• CREATE TABLE AS SELECT — create table from query results
• ALTER TABLE — add/drop/rename columns and constraints
• DROP TABLE IF EXISTS — permanent deletion; cannot be undone
• TRUNCATE TABLE — fast all-row deletion; preserves structure
• DDL is usually auto-committed — be careful with DROP

Next up: Constraints & Data Integrity — deep dive into how constraints protect your data quality.