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 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.
| student_id PK | name | 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 |
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] );
Here's the minimal, working example — creating a
students table:
CREATE TABLE students (
student_id INT,
name VARCHAR(100),
email VARCHAR(150),
city VARCHAR(60),
created_at DATE
);
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).
| 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!) |
| 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 |
| 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 |
INT ·
Money → DECIMAL(10,2) ·
Names/emails → VARCHAR(n) ·
Long text → TEXT ·
Dates → DATE ·
Timestamps → TIMESTAMP DEFAULT CURRENT_TIMESTAMP
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.
student_id INT AUTO_INCREMENT PRIMARY KEY
CURRENT_TIMESTAMP.
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
grade CHAR(2) CHECK (grade IN ('A','B','C','D','F'))
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:
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)
);
SERIAL or
GENERATED ALWAYS AS IDENTITY.
'active'.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.
-- 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
);
| student_id
|
name | |
|---|---|---|
| 1 | Priya Sharma | priya@mail.com |
| 2 | Rahul Patel | rahul@mail.com |
| 3 | Anika Desai | anika@mail.com |
| enroll_id | student_id |
course | grade |
|---|---|---|---|
| 101 | 1 | Python | A |
| 102 | 2 | Web Dev | B+ |
| 103 | 1 | SQL | A+ |
ALTER TABLE … DROP FOREIGN KEY fk_student
if needed.
students table, all of their enrolment rows
in enrollments are automatically deleted too.
Prevents orphaned records.
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.
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.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
amount DECIMAL(10,2)
NOT NULL
CHECK(amount > 0),
status VARCHAR(20)
DEFAULT 'pending'
);
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:
-- 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;
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:
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
);
teachers and
students first — they don't reference any
other table, so order doesn't matter between them.
courses references
teachers, so it comes after teachers. The FK
validation happens at creation time.
enrollments references
both students and
courses, so it must be created last.
It also has a composite UNIQUE to prevent duplicate enrolments.
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.
students · courses · enrollments
· Try:
Which constraint ensures no two rows share the same value in a column, but also prevents NULL values?
- 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
INTfor IDs,VARCHAR(n)for strings,DECIMAL(p,s)for money,DATE/TIMESTAMPfor 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 EXISTSmakes scripts safe to re-run.DESCRIBE table_nameshows the column structure.DROP TABLE IF EXISTSdeletes it permanently.ON DELETE CASCADEandON UPDATE CASCADEon a foreign key keep child table rows in sync with the parent automatically.
Rate Us on Google
Join Our Learning Community
Connect with thousands of learners, get daily tips, ask questions and stay updated with new tutorials.