Creating Tables with DDL: Defining Database Structure

Now that you understand constraints, it's time to put everything together and create complete, production-ready tables. DDL (Data Definition Language) is the subset of SQL used to define database structure—tables, columns, data types, and constraints. The CREATE TABLE statement is where database design becomes reality. In this lesson, you'll learn how to translate your design into SQL, choose appropriate data types, add all necessary constraints, and validate your table structure.

📚 What You'll Learn

• What DDL is and how it differs from DML
• How to define tables using CREATE TABLE
• How to specify column names and data types
• How to add constraints at creation time
• How to create relationships with foreign keys
• Common data types and when to use each
• How to validate table structure after creation
• Best practices for table creation
• Complete examples of real-world tables

1. What is DDL (Data Definition Language)?

DDL vs. DML vs. DQL

In this lesson, we focus on CREATE TABLE (DDL). Later lessons cover DML (INSERT, UPDATE, DELETE) and you've already learned DQL (SELECT).

2. Basic CREATE TABLE Syntax

CREATE TABLE table_name (
    column1_name datatype constraints,
    column2_name datatype constraints,
    column3_name datatype constraints,
    table_constraints
);

Simple Example: Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE
);

Breakdown:

• CREATE TABLE students - Creates a new table named "students"
• student_id INT - Column named student_id, data type INT
• PRIMARY KEY - Makes student_id the primary key
• AUTO_INCREMENT - Automatically assigns next ID
• NOT NULL - Column must have a value
• UNIQUE - No duplicate values allowed
• DEFAULT CURRENT_DATE - Uses today's date if not provided

3. Choosing the Right Data Types

Numeric Data Types

String Data Types

Date and Time Types

Boolean and Other Types

4. Complete Table with All Constraints

Example: E-Commerce Products Table

CREATE TABLE products (
    -- Primary key
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Basic information
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    
    -- Pricing
    price DECIMAL(10, 2) NOT NULL,
    discount_percent INT DEFAULT 0,
    
    -- Inventory
    stock_quantity INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT chk_positive_price CHECK (price > 0),
    CONSTRAINT chk_valid_discount CHECK (discount_percent BETWEEN 0 AND 100),
    CONSTRAINT chk_valid_stock CHECK (stock_quantity >= 0)
);

This table demonstrates:

• ✅ Auto-incrementing primary key
• ✅ Required fields (NOT NULL)
• ✅ Unique constraint (SKU)
• ✅ Appropriate data types (DECIMAL for money, BOOLEAN for flags)
• ✅ DEFAULT values for common fields
• ✅ Automatic timestamps (created_at, updated_at)
• ✅ Named CHECK constraints for validation

5. Creating Related Tables with Foreign Keys

When creating tables with relationships, always create parent tables before child tables.

Example: Blog System

Step 1: Create Parent Tables First

-- Parent table: authors
CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Parent table: categories
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

Step 2: Create Child Table with Foreign Keys

-- Child table: posts (references both authors and categories)
CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    
    -- Foreign keys
    author_id INT NOT NULL,
    category_id INT,
    
    -- Status and timestamps
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    CONSTRAINT fk_post_author 
        FOREIGN KEY (author_id) REFERENCES authors(author_id),
    
    CONSTRAINT fk_post_category 
        FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL,
    
    -- Validation constraints
    CONSTRAINT chk_valid_views CHECK (views >= 0)
);

6. Validating Table Structure

View Table Structure

-- Show table structure
DESCRIBE products;
-- or
SHOW COLUMNS FROM products;

-- Show CREATE TABLE statement
SHOW CREATE TABLE products;

Sample Output

+------------------+---------------+------+-----+-------------------+
| Field            | Type          | Null | Key | Default           |
+------------------+---------------+------+-----+-------------------+
| product_id       | int           | NO   | PRI | NULL              |
| name             | varchar(255)  | NO   |     | NULL              |
| sku              | varchar(50)   | NO   | UNI | NULL              |
| price            | decimal(10,2) | NO   |     | NULL              |
| stock_quantity   | int           | YES  |     | 0                 |
| created_at       | timestamp     | YES  |     | CURRENT_TIMESTAMP |
+------------------+---------------+------+-----+-------------------+

List All Tables in Database

-- Show all tables
SHOW TABLES;

-- Show tables with pattern
SHOW TABLES LIKE 'product%';

7. Table Creation Best Practices

8. 🎯 Practice Exercises

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) UNIQUE,
    date_of_birth DATE NOT NULL,
    account_status ENUM('active', 'suspended', 'closed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Age validation (18 years ago from today)
    CONSTRAINT chk_minimum_age CHECK (
        date_of_birth <= DATE_SUB(CURRENT_DATE, INTERVAL 18 YEAR)
    )
);

-- Verify structure
DESCRIBE customers;

-- Test insert
INSERT INTO customers (first_name, last_name, email, date_of_birth)
VALUES ('Alice', 'Johnson', 'alice@example.com', '1995-05-15');

-- Parent table: orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') 
        DEFAULT 'pending',
    
    -- Foreign key to customers
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    
    -- Validation
    CONSTRAINT chk_positive_total CHECK (total_amount > 0)
);

-- Child table: order_items
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    
    -- Foreign keys
    CONSTRAINT fk_item_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,  -- If order deleted, delete items too
    
    CONSTRAINT fk_item_product
        FOREIGN KEY (product_id) REFERENCES products(product_id),
    
    -- Validation
    CONSTRAINT chk_positive_quantity CHECK (quantity > 0),
    CONSTRAINT chk_positive_price CHECK (unit_price > 0)
);

-- Verify structures
DESCRIBE orders;
DESCRIBE order_items;

-- Step 1: Parent table with no dependencies
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    building VARCHAR(100),
    phone VARCHAR(20)
);

-- Step 2: Tables that reference departments
CREATE TABLE professors (
    professor_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    department_id INT NOT NULL,
    hire_date DATE NOT NULL,
    
    CONSTRAINT fk_prof_dept
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    credits INT NOT NULL,
    department_id INT NOT NULL,
    
    CONSTRAINT fk_course_dept
        FOREIGN KEY (department_id) REFERENCES departments(department_id),
    
    CONSTRAINT chk_valid_credits CHECK (credits BETWEEN 1 AND 4)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    major INT,  -- Optional major
    enrollment_year INT NOT NULL,
    
    CONSTRAINT fk_student_major
        FOREIGN KEY (major) REFERENCES departments(department_id),
    
    CONSTRAINT chk_valid_year CHECK (enrollment_year >= 1900)
);

-- Step 3: Junction table that references multiple parents
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    professor_id INT NOT NULL,
    semester VARCHAR(20) NOT NULL,
    grade VARCHAR(2),
    
    CONSTRAINT fk_enroll_student
        FOREIGN KEY (student_id) REFERENCES students(student_id),
    
    CONSTRAINT fk_enroll_course
        FOREIGN KEY (course_id) REFERENCES courses(course_id),
    
    CONSTRAINT fk_enroll_professor
        FOREIGN KEY (professor_id) REFERENCES professors(professor_id),
    
    -- Prevent duplicate enrollments
    UNIQUE (student_id, course_id, semester),
    
    -- Grade validation
    CONSTRAINT chk_valid_grade CHECK (
        grade IN ('A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D', 'F') 
        OR grade IS NULL
    )
);

-- Verify all tables
SHOW TABLES;
DESCRIBE enrollments;

📝 Key Takeaways

• DDL (Data Definition Language) defines database structure
• CREATE TABLE is the core DDL command for defining tables
• Always specify appropriate data types for columns
• Use DECIMAL for money, never FLOAT or DOUBLE
• Include primary keys in every table (usually AUTO_INCREMENT)
• Add NOT NULL to required fields
• Add UNIQUE to fields that must be distinct
• Create parent tables before child tables
• Use named constraints (CONSTRAINT name ...) for clarity
• Include timestamps (created_at, updated_at) in most tables
• Add CHECK constraints for validation at database level
• Use DESCRIBE or SHOW COLUMNS to verify structure
• Follow naming conventions (lowercase, underscores)