← Back to MySQL Course | Chapter 2: Databases & Tables | Lesson 4 of 7

CREATE TABLE

CREATE TABLE is like drawing a blank chart with column headings, such as name and age, and saying what kind of thing goes in each column.

In this page:

  1. Creating a Simple Table
  2. Safely Creating Tables
  3. Adding Primary Keys
  4. Using Auto Increment
  5. Defining Constraints
Syntax
sql 
CREATE TABLE [IF NOT EXISTS] table_name (
  column1 datatype,
  column2 datatype,
  ...
);

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

sql 
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

sql 
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

sql 
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

sql 
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

⚠️ This is MySQL-specific syntax. It cannot run in the browser editor. Practice this on your local MySQL installation.

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

sql 
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL UNIQUE,
  status VARCHAR(20) DEFAULT 'active'
);
Related Topics

DROP TABLE · TRUNCATE TABLE · ALTER TABLE · CREATE DATABASE

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Running CREATE TABLE users twice without IF NOT EXISTS, which fails with a table already exists error.
  2. Forgetting a data type or length, such as name VARCHAR, which is a syntax error.
  3. Creating a table without a primary key, so duplicate rows cannot be prevented or targeted.
🔒

Chapter Quiz — Complete all 7 topics to unlock

0/7 topics done

Complete these topics first:

← Previous Lesson USE Database Next Lesson → DROP TABLE

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.