How to Create a Table in SQL

What you’ll build or solve

You’ll create a table that stores structured data and enforces rules such as uniqueness and required fields.

When this approach works best

This approach works best when:

• You are designing a new database schema.
• You are adding a new feature that requires structured storage.
• You want to enforce rules like required fields or unique values at the database level.

This is not the right approach if the table already exists and only needs modification. In that case, use ALTER TABLE.

Prerequisites

• Access to an SQL database
• Permission to create tables
• A selected database

If needed:

USE database_name;

Step-by-step instructions

Step 1: Write a CREATE TABLE statement with columns, types, and constraints

All table creation happens inside one statement:

CREATE TABLE table_name (
    column definitions
);

You build complexity inside the parentheses.

Start with basic columns and data types

CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    email VARCHAR(150)
);

This defines three columns. At this stage, there are no rules beyond data types.

Add a primary key

A primary key uniquely identifies each row.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

Many systems support auto-generated IDs.

MySQL:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

Primary keys cannot contain duplicates or null values.

Add constraints for data integrity

Constraints enforce rules on your data.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    active BOOLEAN DEFAULT TRUE
);

Here:

• NOT NULL prevents empty values.
• UNIQUE prevents duplicates.
• DEFAULT sets automatic values.

All of these are part of the same CREATE TABLE statement.

Add foreign keys to link tables

Foreign keys connect tables together.

First create the parent table:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Then create the related table:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

The referenced table must already exist.

Prevent errors with IF NOT EXISTS

If you might run the statement more than once, use:

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

This avoids errors if the table already exists.

What to look for

• Choose appropriate data types such as INT, VARCHAR, DATE, or DECIMAL.
• Primary keys uniquely identify rows.
• Constraints such as NOT NULL, UNIQUE, and DEFAULT enforce rules.
• Create parent tables before defining foreign keys.
• Check for syntax errors like missing commas or parentheses.

Examples you can copy

Example 1: Simple users table (MySQL)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

Example 2: Blog posts table

CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at DATE
);

Example 3: Orders linked to customers

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2),
    created_at DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Example 4: Table with defaults and timestamps

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at DATE DEFAULT CURRENT_DATE
);

Common mistakes and how to fix them

Mistake 1: Missing comma between columns

You might write:

CREATE TABLE users (
    id INT PRIMARY KEY
    name VARCHAR(100)
);

Why it breaks:

SQL requires commas between column definitions.

Correct version:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Mistake 2: Creating a foreign key before the parent table exists

You might try:

CREATE TABLE orders (
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Why it breaks:

The referenced table does not exist yet.

Correct approach:

Create the parent table first.

Mistake 3: Using the wrong data type

You might define prices as VARCHAR.

Why it breaks:

Numeric operations such as sorting or summing will not behave correctly.

Correct approach:

price DECIMAL(10,2)

Troubleshooting

If you see “permission denied,” confirm your user has CREATE privileges.

If you see “table already exists,” use IF NOT EXISTS or drop the table first.

If foreign key creation fails, confirm the referenced column is a primary key or unique.

If syntax errors appear, check commas, parentheses, and constraint placement.

Quick recap

• Use one CREATE TABLE statement.
• Define columns and data types inside parentheses.
• Add primary keys and constraints within the same statement.
• Create parent tables before adding foreign keys.
• Use IF NOT EXISTS to avoid duplicate errors.