MySQL Create Table

Overview

Creating tables is fundamental to database design. Tables store data in rows and columns, with each column having a specific data type. This chapter covers how to create tables with various options, constraints, and settings.

Table Concepts

• Row: Single record
• Column: Attribute with data type
• Primary Key: Unique identifier
• Foreign Key: Reference to another table
• Constraint: Rule for data
• Index: Performance structure

CREATE TABLE Syntax

Basic Syntax

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

Simple Example

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Complete Syntax

CREATE TABLE [IF NOT EXISTS] table_name
    [(column_definition_list)]
    [table_options]
    [partition_options]

column_definition_list:
    column_name data_type [data_type_options] [constraints] [, ...]

data_type_options:
    [NOT NULL | NULL]
    [DEFAULT default_value]
    [AUTO_INCREMENT]
    [UNIQUE [KEY] | PRIMARY [KEY]]
    [COMMENT 'string']

table_options:
    ENGINE=storage_engine
    [DEFAULT] CHARACTER SET charset_name
    [DEFAULT] COLLATE collation_name
    AUTO_INCREMENT=value
    COMMENT='string'

Column Definitions

Data Types

CREATE TABLE data_types_demo (
    -- Numeric types
    tiny_col TINYINT,
    small_col SMALLINT,
    medium_col MEDIUMINT,
    int_col INT,
    big_col BIGINT,
    float_col FLOAT,
    double_col DOUBLE,
    decimal_col DECIMAL(10, 2),
    
    -- String types
    char_col CHAR(10),
    varchar_col VARCHAR(100),
    text_col TEXT,
    mediumtext_col MEDIUMTEXT,
    longtext_col LONGTEXT,
    
    -- Date/Time types
    date_col DATE,
    time_col TIME,
    datetime_col DATETIME,
    timestamp_col TIMESTAMP,
    year_col YEAR,
    
    -- Other types
    bool_col BOOLEAN,
    json_col JSON
);

Column Options

CREATE TABLE column_options_demo (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) UNIQUE,
    status VARCHAR(20) DEFAULT 'active',
    priority INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    description TEXT COMMENT 'User provided description',
    deleted_at TIMESTAMP NULL
);

AUTO_INCREMENT

CREATE TABLE auto_increment_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    
    -- Set starting value
    other_id INT AUTO_INCREMENT = 1000
);

-- Change auto_increment value
ALTER TABLE table_name AUTO_INCREMENT = 1000;

Constraints

PRIMARY KEY

-- Single column primary key
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Composite primary key
CREATE TABLE orders (
    user_id INT,
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (user_id, order_id)
);

-- Add primary key to existing table
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

FOREIGN KEY

-- Create parent table
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

-- Create child table with foreign key
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- Foreign key options
-- ON DELETE: RESTRICT, CASCADE, SET NULL, NO ACTION
-- ON UPDATE: RESTRICT, CASCADE, SET NULL, NO ACTION

UNIQUE Constraint

-- Single column unique
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

-- Multiple columns unique
CREATE TABLE user_emails (
    user_id INT,
    email_type VARCHAR(20),
    email VARCHAR(255),
    UNIQUE (user_id, email_type)
);

-- Add unique constraint
ALTER TABLE table_name ADD UNIQUE (column_name);
ALTER TABLE table_name ADD UNIQUE KEY unique_name (column1, column2);

NOT NULL Constraint

CREATE TABLE not_null_demo (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    age INT NOT NULL DEFAULT 18
);

CHECK Constraint

-- MySQL 8.0+ CHECK constraint
CREATE TABLE check_demo (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150),
    price DECIMAL(10, 2) CHECK (price >= 0),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'pending'))
);

-- Named constraint
CREATE TABLE named_check_demo (
    id INT PRIMARY KEY,
    age INT,
    CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150)
);

-- Add check constraint
ALTER TABLE table_name ADD CHECK (column_name > 0);

DEFAULT Value

CREATE TABLE defaults_demo (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    priority INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    tags JSON DEFAULT '[]'
);

Table Options

Storage Engine

-- InnoDB (default in MySQL 8.0)
CREATE TABLE innodb_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

-- MyISAM
CREATE TABLE myisam_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=MyISAM;

-- Memory (for temporary data)
CREATE TABLE memory_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=Memory;

Character Set and Collation

CREATE TABLE charset_demo (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

AUTO_INCREMENT

CREATE TABLE auto_inc_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
) AUTO_INCREMENT = 1000;

Comment

CREATE TABLE comment_demo (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) COMMENT = 'This table stores user names';

Creating Table from Another Table

CREATE TABLE AS / CREATE TABLE AS

-- Create table from SELECT
CREATE TABLE new_table AS
SELECT * FROM existing_table;

-- Create with specific columns
CREATE TABLE new_table AS
SELECT id, name, email FROM existing_table;

-- Create empty table with structure
CREATE TABLE new_table LIKE existing_table;

-- Copy structure and data
CREATE TABLE new_table AS
SELECT * FROM existing_table;

-- With WHERE condition
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

Temporary Tables

-- Session temporary table
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(100)
);

-- Transaction-scoped temporary table
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value DECIMAL(10, 2)
) ENGINE=InnoDB;

Examples

User Table

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash CHAR(60) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    last_login_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_users_email (email),
    INDEX idx_users_status (status),
    INDEX idx_users_created_at (created_at)
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  COMMENT='User accounts table';

Order Table

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    subtotal DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(10, 2) DEFAULT 0,
    shipping_cost DECIMAL(10, 2) DEFAULT 0,
    discount DECIMAL(10, 2) DEFAULT 0,
    total DECIMAL(10, 2) NOT NULL,
    shipping_address JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_created (created_at)
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  COMMENT='Customer orders';

Managing Tables

Show Tables

-- List tables
SHOW TABLES;

-- List tables with pattern
SHOW TABLES LIKE 'user%';

-- List tables from specific database
SHOW TABLES FROM database_name;

-- Describe table structure
DESCRIBE table_name;
DESC table_name;
SHOW CREATE TABLE table_name;

Alter Table

-- Add column
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(100);

-- Add column with position
ALTER TABLE table_name ADD COLUMN new_column INT AFTER existing_column;
ALTER TABLE table_name ADD COLUMN new_column INT FIRST;

-- Drop column
ALTER TABLE table_name DROP COLUMN column_name;

-- Modify column
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(200);

-- Rename column
ALTER TABLE table_name CHANGE COLUMN old_name new_name VARCHAR(100);

-- Add constraint
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(id);

-- Drop constraint
ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

-- Rename table
RENAME TABLE old_name TO new_name;
ALTER TABLE old_name RENAME TO new_name;

Drop Table

-- Drop table
DROP TABLE table_name;

-- Drop if exists
DROP TABLE IF EXISTS table_name;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

-- Drop with CASCADE (in some contexts)
DROP TABLE table_name CASCADE;

Summary

Creating tables in MySQL involves:

• Column Definitions: Data types and options
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
• Table Options: Engine, character set, comments
• Temporary Tables: Session-scoped tables
• Table Management: Show, alter, drop

---

Previous: Select Database

Next: Drop Table