DDL — Data Definition Language

DDL commands define and manage the structure of database objects. Where DML commands (INSERT, UPDATE, DELETE) work with data inside tables, DDL commands work with the tables, columns, indexes, and schemas themselves — the containers that hold the data.

The four main DDL commands are:

CREATE TABLE

CREATE TABLE defines a new table: its name, columns, data types, and constraints.

Basic Syntax

CREATE TABLE table_name (
    column1_name  datatype  [constraints],
    column2_name  datatype  [constraints],
    ...
    [table_level_constraints]
);

Your First Table

CREATE TABLE employees (
    emp_id      INT              PRIMARY KEY,
    name        VARCHAR(100)     NOT NULL,
    email       VARCHAR(200)     UNIQUE,
    salary      DECIMAL(10,2)    CHECK (salary > 0),
    hire_date   DATE             DEFAULT CURRENT_DATE,
    dept_id     INT,
    is_active   BOOLEAN          DEFAULT TRUE
);

Inline vs Out-of-Line Constraints

Inline (column-level) — constraint is written on the same line as the column:

CREATE TABLE products (
    product_id  INT          PRIMARY KEY,           -- inline
    name        VARCHAR(200) NOT NULL,              -- inline
    price       DECIMAL(8,2) CHECK (price >= 0),    -- inline
    category_id INT          REFERENCES categories  -- inline FK
);

Out-of-line (table-level) — constraint is written separately, after all columns. Required for composite (multi-column) constraints:

CREATE TABLE order_items (
    order_id    INT         NOT NULL,
    product_id  INT         NOT NULL,
    quantity    INT         DEFAULT 1,
    unit_price  DECIMAL(10,2) NOT NULL,
    -- Out-of-line constraints:
    CONSTRAINT pk_order_items    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_oi_order       FOREIGN KEY (order_id)    REFERENCES orders(order_id),
    CONSTRAINT fk_oi_product     FOREIGN KEY (product_id)  REFERENCES products(product_id),
    CONSTRAINT chk_qty_positive  CHECK (quantity > 0)
);

Data Types — Comprehensive Reference

Choosing the right data type for each column is one of the most important design decisions. Using the wrong type wastes storage, causes implicit conversions that slow down queries, and allows invalid data.

Integer Types

Decimal / Exact Numeric Types

Character / String Types

VARCHAR vs CHAR:

-- CHAR(5) always stores exactly 5 characters
-- 'US' stored as 'US   ' (padded with 3 spaces)
country_code  CHAR(2)      -- Good for ISO country codes ('US', 'GB', 'DE')
gender        CHAR(1)      -- Good for single-character codes ('M', 'F', 'O')

-- VARCHAR(100) stores only as many characters as needed
-- 'Alice' stored as 'Alice' (5 characters, not padded)
name          VARCHAR(100) -- Good for variable-length names
email         VARCHAR(200) -- Good for email addresses

Date and Time Types

Boolean Type

-- PostgreSQL / MySQL
is_active  BOOLEAN  DEFAULT TRUE

-- Oracle workaround
is_active  NUMBER(1)  DEFAULT 1  CHECK (is_active IN (0, 1))
-- or
is_active  CHAR(1)    DEFAULT 'Y' CHECK (is_active IN ('Y', 'N'))

Large Object Types (LOBs)

For very large content (documents, images, binary files):

CREATE TABLE documents (
    doc_id    INT   PRIMARY KEY,
    title     VARCHAR(500) NOT NULL,
    body      CLOB,           -- Oracle: large text content
    attachment BLOB,          -- Oracle: binary file
    thumbnail  BLOB           -- Oracle: binary image
);

Data Type Comparison Across Databases

Auto-Increment Primary Keys

A very common pattern is a primary key that automatically increments with each new row:

-- MySQL / MariaDB
CREATE TABLE users (
    user_id  INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- PostgreSQL: SERIAL shorthand
CREATE TABLE users (
    user_id  SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- PostgreSQL / SQL Server / Oracle 12c+: IDENTITY column
CREATE TABLE users (
    user_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Oracle pre-12c: use a SEQUENCE + trigger
CREATE SEQUENCE seq_users START WITH 1 INCREMENT BY 1;
CREATE TABLE users (
    user_id  INT DEFAULT seq_users.NEXTVAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE AS SELECT (CTAS)

CTAS creates a new table and populates it with the result of a SELECT query. It is useful for:

• Creating summary/report tables
• Making a backup copy of data
• Creating a subset of a large table for testing

-- Create a new table with all IT department employees
CREATE TABLE it_employees AS
SELECT *
FROM   employees
WHERE  dept = 'IT';

-- Create a summary table (aggregated data)
CREATE TABLE dept_summary AS
SELECT dept,
       COUNT(*)        AS headcount,
       AVG(salary)     AS avg_salary,
       SUM(salary)     AS total_payroll
FROM   employees
GROUP BY dept;

-- Copy table structure WITHOUT data (using an always-false WHERE)
CREATE TABLE employees_backup AS
SELECT * FROM employees WHERE 1 = 0;
-- Result: empty table with the same columns and types as employees
-- Note: constraints are NOT copied (no PRIMARY KEY, FOREIGN KEY, etc.)

ALTER TABLE — Modifying Existing Tables

ALTER TABLE lets you change a table's structure after it has been created — without dropping and recreating it (and losing all the data).

Adding Columns

-- Add a single column
ALTER TABLE employees ADD phone VARCHAR(20);

-- Add a column with a default and constraint
ALTER TABLE employees ADD bonus DECIMAL(10,2) DEFAULT 0 CHECK (bonus >= 0);

-- Add multiple columns (MySQL / Oracle)
ALTER TABLE employees
ADD hire_date DATE,
ADD department_notes TEXT;

-- Add multiple columns (PostgreSQL)
ALTER TABLE employees
ADD COLUMN manager_id INT,
ADD COLUMN performance_rating INT CHECK (performance_rating BETWEEN 1 AND 5);

Modifying Columns

-- Change a column's data type (Oracle / MySQL)
ALTER TABLE employees MODIFY salary NUMBER(12, 2);

-- Change a column's data type (PostgreSQL / SQL Server)
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- Increase VARCHAR length (safe — won't lose data)
ALTER TABLE employees MODIFY name VARCHAR(200);   -- Oracle
ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR(200);  -- PostgreSQL

-- Decreasing length is risky — only works if no existing values exceed the new limit

-- Add NOT NULL (column must have no NULLs first)
ALTER TABLE employees MODIFY salary NOT NULL;   -- Oracle
ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;  -- PostgreSQL

Renaming Columns

-- Oracle / PostgreSQL / MySQL 8+
ALTER TABLE employees RENAME COLUMN phone TO phone_number;

-- SQL Server
EXEC sp_rename 'employees.phone', 'phone_number', 'COLUMN';

Dropping Columns

-- Remove a column permanently (data is gone!)
ALTER TABLE employees DROP COLUMN notes;

-- Drop multiple columns (MySQL)
ALTER TABLE employees DROP COLUMN notes, DROP COLUMN temp_code;

Renaming a Table

-- Oracle
ALTER TABLE products RENAME TO inventory;

-- PostgreSQL
ALTER TABLE products RENAME TO inventory;

-- MySQL
RENAME TABLE products TO inventory;

-- SQL Server
EXEC sp_rename 'products', 'inventory';

Adding and Dropping Constraints via ALTER TABLE

-- Add a PRIMARY KEY
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (emp_id);

-- Add a FOREIGN KEY
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE SET NULL;

-- Add a CHECK constraint
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price >= 0);

-- Add a UNIQUE constraint
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Drop any named constraint
ALTER TABLE employees DROP CONSTRAINT fk_emp_dept;
ALTER TABLE products   DROP CONSTRAINT chk_price;
ALTER TABLE users      DROP CONSTRAINT uq_users_email;

-- Drop a PRIMARY KEY
ALTER TABLE employees DROP CONSTRAINT pk_employees;

DROP TABLE — Removing a Table Permanently

DROP TABLE removes the entire table — structure and all data — permanently.

-- Drop the table (will error if it does not exist)
DROP TABLE old_archive;

-- Drop only if it exists (prevents error in scripts)
DROP TABLE IF EXISTS old_archive;    -- PostgreSQL, MySQL, SQL Server

-- Oracle: does not have IF EXISTS, use a procedure or catch the error

-- Drop with CASCADE to remove dependent objects (views, FK references)
DROP TABLE departments CASCADE CONSTRAINTS;  -- Oracle
DROP TABLE departments CASCADE;              -- PostgreSQL

TRUNCATE TABLE — Removing All Rows

TRUNCATE removes all rows from a table but keeps the table structure intact. It is much faster than DELETE FROM table for clearing large tables because it does not log individual row deletions.

-- Remove all rows (structure remains)
TRUNCATE TABLE temp_staging;

-- Oracle variant
TRUNCATE TABLE temp_staging REUSE STORAGE;  -- keeps allocated disk space

-- PostgreSQL: can truncate multiple tables and cascade
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

TRUNCATE vs DELETE vs DROP

-- DELETE: can be rolled back, can have WHERE clause
BEGIN;
DELETE FROM employees WHERE dept = 'IT';
ROLLBACK;   -- IT employees are back!

-- TRUNCATE: cannot be rolled back (in most databases), no WHERE
TRUNCATE TABLE employees;
-- All rows gone, cannot roll back, but structure remains

-- DROP: everything gone
DROP TABLE employees;
-- Table no longer exists

Table Design Best Practices

Naming Conventions

-- Tables: plural nouns, lowercase, underscore-separated
employees     orders     order_items    product_categories

-- Columns: singular nouns, lowercase, underscore-separated
emp_id        first_name    order_date    is_active

-- Primary keys: tablename_id (or just id)
emp_id        order_id      product_id

-- Foreign keys: match the referenced primary key's name
-- employees.dept_id references departments.dept_id (same name)

-- Boolean columns: start with is_, has_, can_
is_active     has_children    can_edit

-- Timestamp columns: end with _at or _date
created_at    updated_at    order_date    birth_date

Surrogate vs Natural Keys

Surrogate key — a system-generated ID (usually an auto-increment integer or UUID) that has no business meaning:

emp_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Natural key — a real-world identifier that is meaningful to the business:

passport_number  CHAR(9)  PRIMARY KEY
-- OR
ssn              CHAR(11) PRIMARY KEY

Recommendation: Use surrogate keys for almost everything. Natural keys seem convenient but:

• Passport numbers can change (renewal)
• SSNs are sensitive data that should not be used as join keys
• Natural keys from external systems are not under your control

-- Good table design with surrogate key
CREATE TABLE employees (
    emp_id          INT              GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_number VARCHAR(20)      UNIQUE NOT NULL,   -- natural key as UNIQUE, not PK
    first_name      VARCHAR(100)     NOT NULL,
    last_name       VARCHAR(100)     NOT NULL,
    email           VARCHAR(200)     UNIQUE NOT NULL,
    salary          DECIMAL(12, 2)   NOT NULL CHECK (salary > 0),
    hire_date       DATE             NOT NULL DEFAULT CURRENT_DATE,
    dept_id         INT              REFERENCES departments(dept_id) ON DELETE SET NULL,
    is_active       BOOLEAN          NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Choosing Column Data Types

-- IDs and counts: use INT (or BIGINT for very large tables)
emp_id          INT
order_count     INT

-- Money: always use DECIMAL/NUMERIC, never FLOAT
price           DECIMAL(10, 2)
tax_amount      DECIMAL(12, 4)  -- more precision for tax

-- Short text: VARCHAR with a reasonable maximum
name            VARCHAR(200)
email           VARCHAR(254)   -- 254 is the official max email length per RFC 5321
phone           VARCHAR(20)    -- include country code, brackets, dashes

-- Fixed-length codes: use CHAR
country_code    CHAR(2)        -- ISO 3166: 'US', 'GB', 'DE'
currency_code   CHAR(3)        -- ISO 4217: 'USD', 'EUR', 'GBP'
gender          CHAR(1)        -- 'M', 'F', 'O', 'U'

-- Dates: DATE for dates, TIMESTAMP for date+time
birth_date      DATE
order_placed_at TIMESTAMP

-- Large text: TEXT or CLOB for unlimited content
description     TEXT
article_body    TEXT

Common Errors

Interview Corner

ALTER TABLE employees DROP COLUMN middle_name;

-- Any view or procedure referencing MIDDLE_NAME is now INVALID
SELECT object_name, object_type, status
FROM   user_objects
WHERE  status = 'INVALID';

Related Topics

• Constraints — constraints defined alongside or after table creation
• DML Commands — INSERT, UPDATE, DELETE, MERGE to manipulate the data in created tables
• Indexes — index creation and maintenance (CREATE INDEX, REBUILD)
• Sequences & Synonyms — auto-increment sequences and name aliases
• Data Dictionary — USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS to inspect schema