CREATE TABLE
In this page:
Creating a Simple Table
CREATE TABLE defines a table's name, its columns, and each column's data type in one statement, forming the structural blueprint every row will follow from that point on. Choosing the right data type for each column upfront matters, since changing it later on a large table can be a slow, disruptive operation.
Example: Creating a Simple Table
CREATE TABLE users (
id INT,
name VARCHAR(50),
age INT
);Safely Creating Tables
IF NOT EXISTS after CREATE TABLE prevents a script from erroring out if the table is already present, which is standard practice for setup scripts meant to run safely more than once.
Example: Safely Creating Tables
CREATE TABLE IF NOT EXISTS users (id INT, name VARCHAR(50));Adding Primary Keys
A PRIMARY KEY column uniquely identifies each row and can never hold NULL, giving MySQL a reliable way to locate, join, and index individual records efficiently. Most tables use a single-column PRIMARY KEY, often paired with AUTO_INCREMENT, though a composite key across multiple columns is also possible.
Example: Adding Primary Keys
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);Using Auto Increment
AUTO_INCREMENT tells MySQL to generate the next sequential integer automatically on insert, so you never have to manually track or supply the next available ID yourself. This is almost always paired with a PRIMARY KEY column, since auto-generated IDs are the most common way to uniquely identify rows.
Example: Using Auto Increment
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);Defining Constraints
Column-level constraints like NOT NULL, UNIQUE, and DEFAULT enforce data-quality rules at the database layer itself, catching bad data even if application-level validation is ever skipped or buggy.
Example: Defining Constraints
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
status VARCHAR(20) DEFAULT 'active'
);Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: