CREATE TABLE Essentials

CREATE TABLE Syntax

The CREATE TABLE statement defines a new table with its columns and constraints.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

💡 Always plan your table structure before creating it. Define what data each column will hold and what constraints are needed.

Complete Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    salary DECIMAL(10,2) CHECK (salary > 0),
    department_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Column Constraints

NOT NULL

Ensures the column cannot contain NULL values.

CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL
);

UNIQUE

Ensures all values in the column are different.

CREATE TABLE customers (
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

PRIMARY KEY

Combines NOT NULL and Unique. Uniquely identifies each row.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

FOREIGN KEY

Links to the primary key of another table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

DEFAULT

Provides a default value when none is specified.

CREATE TABLE settings (
    theme VARCHAR(20) DEFAULT 'light',
    notifications BOOLEAN DEFAULT TRUE
);

CHECK

Validates that values meet a condition.

CREATE TABLE employees (
    age INT CHECK (age >= 18 AND age <= 120),
    salary DECIMAL(10,2) CHECK (salary > 0),
    department VARCHAR(50) CHECK (department IN ('HR', 'IT', 'Sales'))
);

AUTO_INCREMENT

Automatically generates unique sequential numbers.

CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10,2)
);

-- invoice_id auto-generates: 1, 2, 3, ...
INSERT INTO invoices (amount) VALUES (100.00);
INSERT INTO invoices (amount) VALUES (250.00);

Composite Primary Key

Uses multiple columns together as the primary key.

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

⚠️ Composite keys work but can complicate foreign key relationships. Consider using a surrogate key (single INT column) for simplicity.

Creating Tables from Existing Tables

-- Copy structure and data
CREATE TABLE customers_backup AS
SELECT * FROM customers;

-- Copy structure only (no data)
CREATE TABLE customers_archive AS
SELECT * FROM customers WHERE 1 = 0;

Modifying Tables After Creation

-- Add a column
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(50);

-- Drop a column
ALTER TABLE employees DROP COLUMN middle_name;

-- Modify column type
ALTER TABLE employees MODIFY COLUMN phone VARCHAR(30);

Dropping Tables

-- Remove table and all data
DROP TABLE IF EXISTS temp_data;

-- Remove all data but keep structure
TRUNCATE TABLE temp_data;

⚠️ DROP TABLE permanently deletes the table and data. TRUNCATE removes all rows but keeps the structure. Always backup before dropping tables.

✏️ Exercise: Create a 'courses' table with: course_id (auto-increment primary key), course_name (not null, unique), credits (1-5), department (not null), instructor_name.

See Solution

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL UNIQUE,
    credits INT CHECK (credits >= 1 AND credits <= 5),
    department VARCHAR(50) NOT NULL,
    instructor_name VARCHAR(100)
);

✅ Key Takeaways

1. Always define PRIMARY KEY for unique row identification
2. Use NOT NULL for required fields and UNIQUE for identifier fields
3. Apply CHECK constraints to validate business rules at the database level
4. Use DEFAULT values for common defaults to simplify INSERT statements
5. AUTO_INCREMENT generates sequential IDs automatically