CREATE TABLE defines a new table — its columns, their data types, and the rules (constraints) each column must follow.
Syntax
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);Example
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2) DEFAULT 0,
joined_on DATE
);This creates an empty table — no rows yet, just structure. You'd add data with INSERT.
Auto-Incrementing Primary Keys — Dialect Differences
| Database | Syntax |
|---|---|
| MySQL | id INT AUTO_INCREMENT PRIMARY KEY |
| PostgreSQL | id SERIAL PRIMARY KEY (or GENERATED ALWAYS AS IDENTITY in modern Postgres) |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY |
Avoiding Errors on Re-Run
CREATE TABLE IF NOT EXISTS employees ( ... );Practical Use Case
This is the first statement in almost every real project setup — defining the schema before any application code touches the database. Getting data types and constraints right here saves painful migrations later.
Common Mistakes
- Forgetting a
PRIMARY KEY— every table should have one to uniquely identify rows - Making every column
VARCHAR(255)without thinking about the actual data (see Data Types) - Not adding
NOT NULLon columns that should never be empty (e.g. an order'scustomer_id)
Interview Relevance
You'll frequently be asked to design a table from a description ("design a table to store orders for an e-commerce store") — practice reading requirements and mapping them to columns, types, and constraints.
Practice Question
Design a students table with: an auto-incrementing ID, a required name, an email that must be unique, and an enrollment date that defaults to today.