Tutorials SQL CREATE TABLE
Ch 7 / 69
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above
Chapter 7 Beginner 7 min read DDL

SQL CREATE TABLE

A database without tables is like a filing cabinet without folders — useless. CREATE TABLE is the DDL command that defines the structure of your data: which columns exist, what data types they hold, and which rules (constraints) enforce data integrity. In this chapter you'll master everything from the minimal syntax to multi-table designs with foreign keys.

What you'll learn
Full syntax of CREATE TABLE with all clauses
Choosing the right SQL data type for each column
All 6 constraint types — PK, NOT NULL, UNIQUE, DEFAULT, CHECK, FK
Defining column-level vs table-level constraints
Using IF NOT EXISTS to make scripts idempotent
Real-world multi-table design with foreign keys
SHOW TABLES, DESCRIBE, and DROP TABLE

What is a Table in SQL?

A table is the fundamental storage unit of a relational database. It organises data into rows (records) and columns (fields), just like a spreadsheet — but with strict rules that guarantee the data stays accurate, consistent, and queryable at any scale.

Every table belongs to a database (the container you created in Chapter 6). A single database typically contains dozens of related tables — for example, a school application might have tables for students, courses, teachers, and enrollments.

Anatomy of a SQL Table
student_id PK name email city created_at
1 Priya Sharma priya@mail.com Vadodara 2024-01-15
2 Rahul Patel rahul@mail.com Surat 2024-01-18
3 Anika Desai anika@mail.com Vadodara 2024-02-03
Column (field) — one type of data per column
Row (record) — one complete entry
Primary Key — uniquely identifies each row

CREATE TABLE — Full Syntax Explained

The CREATE TABLE statement defines the table name, every column's name and data type, and optional constraints that enforce data rules. Here is the complete syntax with all parts labelled:

CREATE TABLE [IF NOT EXISTS] table_name (
    column1   datatype  [column_constraints],
    column2   datatype  [column_constraints],
    column3   datatype  [column_constraints],
    ...
    [table_constraints]
);
Blue keywords — required SQL keywords Purple — your table name Green — your column names Yellow — data types Orange — constraints (optional but important)

Here's the minimal, working example — creating a students table:

SQL — Minimal CREATE TABLE (no constraints)
CREATE TABLE students (
    student_id   INT,
    name         VARCHAR(100),
    email        VARCHAR(150),
    city         VARCHAR(60),
    created_at   DATE
);
Coming Up
This table works, but it has no rules — you could insert duplicate students or leave the name empty. The constraints section below shows how to fix that and produce a production-quality table definition.

Choosing the Right SQL Data Type

Every column must have a data type — it tells the database what kind of value is allowed and how much space to allocate. Choosing the right type matters: it affects storage size, query speed, and data validity (you can't store "hello" in an INT column).

Numeric Types
Data Type Storage Range / Notes Use For
TINYINT 1 byte –128 to 127  ·  UNSIGNED: 0–255 Boolean flags, small counts
SMALLINT 2 bytes –32,768 to 32,767 Ages, ratings, short codes
INT / INTEGER 4 bytes –2.1 billion to 2.1 billion IDs, counts, quantities ✅ Most used
BIGINT 8 bytes ±9.2 × 10¹⁸ Large IDs, timestamps in ms
DECIMAL(p,s) Variable p = total digits, s = decimal places Money, prices ✅ (exact)
FLOAT / DOUBLE 4 / 8 bytes Approx floating point Scientific data (not money!)
String Types
Data Type Max Length Storage Use For
CHAR(n) 255 Fixed n bytes Fixed-length codes: country codes, status flags
VARCHAR(n) 65,535 Variable + 1–2 bytes Names, emails, addresses ✅ Most used
TEXT 65,535 chars Variable Blog posts, descriptions, notes
MEDIUMTEXT ~16 MB Variable Long articles, HTML content
LONGTEXT ~4 GB Variable Very large documents, logs
ENUM('a','b') 65,535 values 1–2 bytes Status fields with fixed options
Date & Time Types
Data Type Format Range Use For
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31 Birthdates, deadlines, events
TIME HH:MM:SS −838:59:59 to 838:59:59 Durations, opening hours
DATETIME YYYY-MM-DD HH:MM:SS 1000 to 9999 Appointments, order timestamps
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970 to 2038 Row creation/update tracking ✅
YEAR YYYY 1901–2155 Birth year, graduation year
Quick Selection Guide
IDs and counts → INT · Money → DECIMAL(10,2) · Names/emails → VARCHAR(n) · Long text → TEXT · Dates → DATE · Timestamps → TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

The 6 SQL Constraints — Enforcing Data Integrity

Constraints are rules attached to columns (or entire tables) that the database enforces automatically on every INSERT, UPDATE, and DELETE operation. They are the most powerful tool for keeping your data clean and consistent.

PRIMARY KEY
Primary Key
Uniquely identifies every row. Implicitly NOT NULL and UNIQUE. Every table should have exactly one primary key.
student_id INT PRIMARY KEY
student_id INT AUTO_INCREMENT PRIMARY KEY
NOT NULL
Not Null
Prevents empty values in a column. Any INSERT that omits this column or passes NULL will be rejected with an error.
name VARCHAR(100) NOT NULL
UNIQUE
Unique
No two rows can share the same value in this column. Unlike PRIMARY KEY, the column can contain NULL (multiple NULLs are usually allowed).
email VARCHAR(150) UNIQUE
DEFAULT
Default
Sets a fallback value used when no value is provided during INSERT. Accepts literals or SQL functions like CURRENT_TIMESTAMP.
status VARCHAR(20) DEFAULT 'active'
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
CHECK
Check
Validates that a value satisfies a condition before it is stored. Rejects any insert or update where the condition evaluates to FALSE.
age INT CHECK (age >= 0 AND age <= 120)
grade CHAR(2) CHECK (grade IN ('A','B','C','D','F'))
FOREIGN KEY
Foreign Key
Links a column to the PRIMARY KEY of another table, enforcing referential integrity. You can't insert a value that doesn't exist in the referenced table.
FOREIGN KEY (student_id)
REFERENCES students(student_id)

Production-Quality CREATE TABLE Example

Now let's combine everything — a complete students table definition with proper data types, all essential constraints, auto-increment, and automatic timestamps:

SQL — Full students table with all constraints
CREATE TABLE IF NOT EXISTS students (
    -- Primary key: auto-increments from 1, 2, 3...
    student_id  INT            NOT NULL AUTO_INCREMENT,
    -- Required text columns
    name        VARCHAR(100)   NOT NULL,
    email       VARCHAR(150)   NOT NULL UNIQUE,
    phone       VARCHAR(15),
    city        VARCHAR(60)    NOT NULL,
    -- Validated with CHECK
    age         TINYINT UNSIGNED               CHECK (age >= 5 AND age <= 100),
    grade       CHAR(2)                        CHECK (grade IN ('A+','A','B+','B','C','D','F')),
    -- Default values
    status      ENUM('active','inactive','graduated')  NOT NULL DEFAULT 'active',
    enrolled_on DATE           NOT NULL DEFAULT (CURRENT_DATE),
    created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- Table-level primary key declaration
    PRIMARY KEY (student_id)
);
AUTO_INCREMENT
Tells MySQL to automatically assign the next integer value (1, 2, 3…) when a new row is inserted without specifying the ID. PostgreSQL equivalent: SERIAL or GENERATED ALWAYS AS IDENTITY.
NOT NULL DEFAULT 'active'
Constraints can be combined on the same column. This means the column is required, but if no value is supplied, the database automatically uses 'active'.
ON UPDATE CURRENT_TIMESTAMP
A MySQL-specific extension: every time the row is updated, this column automatically refreshes to the current timestamp. Essential for audit trails.
Table-level PRIMARY KEY (student_id)
The primary key can be declared at the bottom of the table definition (table-level) rather than inline with the column. Required for composite keys — primary keys spanning multiple columns.

Linked Tables with Foreign Keys

The real power of relational databases comes from linking tables together via foreign keys. A foreign key in one table references the primary key of another, creating a guaranteed relationship between the data.

Rule: Always create the referenced table first. In the example below, students must exist before enrollments is created, because enrollments references it.

SQL — Two linked tables (students ↔ enrollments)
-- Step 1: Parent table (must be created first)
CREATE TABLE IF NOT EXISTS students (
    student_id  INT           AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(150)  NOT NULL UNIQUE,
    city        VARCHAR(60)
);

-- Step 2: Child table (references parent)
CREATE TABLE IF NOT EXISTS enrollments (
    enroll_id   INT           AUTO_INCREMENT PRIMARY KEY,
    student_id  INT           NOT NULL,
    course      VARCHAR(100)  NOT NULL,
    grade       CHAR(2),
    enrolled_on DATE          DEFAULT (CURRENT_DATE),
    -- Foreign key constraint
    CONSTRAINT fk_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
students ↔ enrollments — Foreign Key Relationship
students (parent)
student_id name email
1 Priya Sharma priya@mail.com
2 Rahul Patel rahul@mail.com
3 Anika Desai anika@mail.com
enrollments (child)
enroll_id student_id course grade
101 1 Python A
102 2 Web Dev B+
103 1 SQL A+
students.student_id → enrollments.student_id (Foreign Key)
CONSTRAINT fk_student
Gives the foreign key constraint a meaningful name. This makes error messages readable and allows you to later ALTER TABLE … DROP FOREIGN KEY fk_student if needed.
ON DELETE CASCADE
When a student is deleted from the students table, all of their enrolment rows in enrollments are automatically deleted too. Prevents orphaned records.
ON UPDATE CASCADE
If a student's ID changes in the parent table, all matching foreign key values in child tables automatically update. Less common but useful in some schemas.
Referential Integrity in Action
Trying to insert an enrolment with student_id = 99 when no student with ID 99 exists will fail immediately with a foreign key constraint error. This is exactly what you want — the database guards your data automatically.
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

Column-Level vs Table-Level Constraints

SQL constraints can be declared in two places: inline with the column (column-level), or at the bottom of the table definition (table-level). Understanding the difference is important — some constraints must be declared at the table level.

Column-Level (inline)
CREATE TABLE orders (
    order_id  INT  PRIMARY KEY,
    amount    DECIMAL(10,2)
              NOT NULL
              CHECK(amount > 0),
    status    VARCHAR(20)
              DEFAULT 'pending'
);
Table-Level (end of definition)
CREATE TABLE order_items (
    order_id    INT  NOT NULL,
    product_id  INT  NOT NULL,
    quantity    INT  NOT NULL,
    -- Composite primary key
    PRIMARY KEY (order_id, product_id),
    -- Named foreign key
    CONSTRAINT fk_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
);
Aspect Column-Level Table-Level
Placement After the column's data type After all column definitions
Applies to Single column only One or multiple columns
Composite PRIMARY KEY ❌ Not possible ✅ Required for multi-column PKs
FOREIGN KEY ❌ Not standard ✅ Must be table-level
Named constraints Not always supported ✅ Fully supported with CONSTRAINT name
Readability Compact, easy to scan Verbose but very explicit

IF NOT EXISTS, SHOW TABLES & DROP TABLE

Three essential commands you'll use constantly alongside CREATE TABLE:

SQL — IF NOT EXISTS, SHOW, DESCRIBE, DROP
-- Safe creation: no error if table already exists
CREATE TABLE IF NOT EXISTS students ( ... );

-- List all tables in the current database
SHOW TABLES;

-- Show the column structure of a table
DESCRIBE students;
DESC students;             -- shorthand

-- Show the exact CREATE TABLE statement used
SHOW CREATE TABLE students;

-- Permanently delete a table and all its data
DROP TABLE students;

-- Safe drop: no error if table doesn't exist
DROP TABLE IF EXISTS students;
IF NOT EXISTS
Makes your setup scripts re-runnable — the server skips creation silently if the table already exists. Critical for team projects and CI/CD pipelines.
DESCRIBE / DESC
Instantly shows all columns, their data types, NULL/NOT NULL flags, keys, defaults, and extra options. Your most-used inspection command.
DROP TABLE
Permanently deletes the table and all its data. Unlike TRUNCATE (which removes data but keeps the structure), DROP removes everything with no undo.
SHOW CREATE TABLE
Outputs the exact SQL that would recreate the table. Useful for auditing, migrating to another server, or understanding a table you didn't create.

Real-World Example — School Database Schema

Here's a complete, production-ready schema for a school application — four tables with primary keys, foreign keys, constraints, and defaults. This is what real SQL looks like in practice:

SQL — Complete school_db schema (4 tables)
USE school_db;

-- 1. Teachers
CREATE TABLE IF NOT EXISTS teachers (
    teacher_id  INT           AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(150)  NOT NULL UNIQUE,
    department  VARCHAR(80),
    joined_on   DATE          NOT NULL DEFAULT (CURRENT_DATE)
);

-- 2. Students
CREATE TABLE IF NOT EXISTS students (
    student_id  INT           AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(150)  NOT NULL UNIQUE,
    city        VARCHAR(60)   NOT NULL,
    status      ENUM('active','inactive','graduated') NOT NULL DEFAULT 'active',
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);

-- 3. Courses
CREATE TABLE IF NOT EXISTS courses (
    course_id   INT           AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(120)  NOT NULL UNIQUE,
    teacher_id  INT           NOT NULL,
    credits     TINYINT       NOT NULL DEFAULT 3
                              CHECK (credits BETWEEN 1 AND 6),
    CONSTRAINT fk_course_teacher
        FOREIGN KEY (teacher_id)
        REFERENCES teachers(teacher_id)
        ON DELETE RESTRICT
);

-- 4. Enrollments (junction table: many students ↔ many courses)
CREATE TABLE IF NOT EXISTS enrollments (
    enroll_id   INT           AUTO_INCREMENT PRIMARY KEY,
    student_id  INT           NOT NULL,
    course_id   INT           NOT NULL,
    grade       CHAR(2)       CHECK (grade IN ('A+','A','B+','B','C','D','F')),
    enrolled_on DATE          NOT NULL DEFAULT (CURRENT_DATE),
    UNIQUE (student_id, course_id),  -- prevent duplicate enrolments
    CONSTRAINT fk_enroll_student
        FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    CONSTRAINT fk_enroll_course
        FOREIGN KEY (course_id)  REFERENCES courses(course_id)  ON DELETE CASCADE
);
1
Start with standalone tables (no FK)
Create teachers and students first — they don't reference any other table, so order doesn't matter between them.
2
Create tables that reference only already-created ones
courses references teachers, so it comes after teachers. The FK validation happens at creation time.
3
Junction tables come last
enrollments references both students and courses, so it must be created last. It also has a composite UNIQUE to prevent duplicate enrolments.
📊
Free Resource
Download our SQL Cheat Sheet
All CREATE TABLE syntax, constraint types, and data types — one printable page.
Get Sheet

Try It Yourself

This in-browser runner is pre-loaded with the school database — the students, courses, and enrollments tables from the example above, already populated with data. Use SELECT queries to explore the structure and relationships.

SQL Query Runner In-Browser
Tables: students · courses · enrollments  ·  Try:
Results will appear here after you run a query
Quick Check — Chapter 7

Which constraint ensures no two rows share the same value in a column, but also prevents NULL values?

Chapter Summary
  • A table is the core storage unit in SQL — a grid of rows (records) and columns (fields). It belongs to a database and must be created before inserting data.
  • Syntax: CREATE TABLE [IF NOT EXISTS] table_name (col1 datatype [constraints], ...);
  • Every column needs a data type — choose INT for IDs, VARCHAR(n) for strings, DECIMAL(p,s) for money, DATE / TIMESTAMP for dates.
  • Six constraint types enforce data integrity: PRIMARY KEY (unique + not-null ID), NOT NULL (required), UNIQUE (no duplicates), DEFAULT (fallback), CHECK (condition), FOREIGN KEY (link to another table).
  • PRIMARY KEY is unique AND not null — every table should have one. AUTO_INCREMENT auto-assigns the next integer ID on insert.
  • Declare FOREIGN KEY and composite keys at the table level (after all column definitions). Always create the referenced (parent) table first.
  • IF NOT EXISTS makes scripts safe to re-run. DESCRIBE table_name shows the column structure. DROP TABLE IF EXISTS deletes it permanently.
  • ON DELETE CASCADE and ON UPDATE CASCADE on a foreign key keep child table rows in sync with the parent automatically.
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

Rate Us on Google

4.8 / 5
Enjoying this tutorial?
Your 5-star review helps other learners discover our free tutorials and encourages us to keep creating quality content.
80+ reviews
Write a Google Review
Join WhatsApp Channel