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:

CREATETABLE table_name (
column definitions
);

You build complexity inside the parentheses.

Start with basic columns and data types

CREATETABLE users (
    idINT,
    nameVARCHAR(100),
    emailVARCHAR(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.

CREATETABLE users (
    idINTPRIMARYKEY,
    nameVARCHAR(100),
    emailVARCHAR(150)
);

Many systems support auto-generated IDs.

MySQL:

CREATETABLE users (
    idINT AUTO_INCREMENTPRIMARYKEY,
    nameVARCHAR(100),
    emailVARCHAR(150)
);

PostgreSQL:

CREATETABLE users (
    id SERIALPRIMARYKEY,
    nameVARCHAR(100),
    emailVARCHAR(150)
);

Primary keys cannot contain duplicates or null values.

Add constraints for data integrity

Constraints enforce rules on your data.

CREATETABLE users (
    idINTPRIMARYKEY,
    nameVARCHAR(100)NOTNULL,
    emailVARCHAR(150)UNIQUE,
    activeBOOLEANDEFAULTTRUE
);

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:

CREATETABLE customers (
    idINTPRIMARYKEY,
    nameVARCHAR(100)
);

Then create the related table:

CREATETABLE orders (
    idINTPRIMARYKEY,
    customer_idINT,
    totalDECIMAL(10,2),
FOREIGNKEY (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:

CREATETABLEIFNOTEXISTS users (
    idINTPRIMARYKEY,
    nameVARCHAR(100),
    emailVARCHAR(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)

CREATETABLE users (
    idINT AUTO_INCREMENTPRIMARYKEY,
    nameVARCHAR(100)NOTNULL,
    emailVARCHAR(150)UNIQUE
);

Example 2: Blog posts table

CREATETABLE posts (
    idINTPRIMARYKEY,
    titleVARCHAR(200)NOTNULL,
    content TEXT,
    published_atDATE
);

Example 3: Orders linked to customers

CREATETABLE customers (
    idINTPRIMARYKEY,
    nameVARCHAR(100)
);

CREATETABLE orders (
    idINTPRIMARYKEY,
    customer_idINTNOTNULL,
    totalDECIMAL(10,2),
    created_atDATE,
FOREIGNKEY (customer_id)REFERENCES customers(id)
);

Example 4: Table with defaults and timestamps

CREATETABLE tasks (
    idINTPRIMARYKEY,
    titleVARCHAR(150)NOTNULL,
    completedBOOLEANDEFAULTFALSE,
    created_atDATEDEFAULTCURRENT_DATE
);

Common mistakes and how to fix them

Mistake 1: Missing comma between columns

You might write:

CREATETABLE users (
    idINTPRIMARYKEY
    nameVARCHAR(100)
);

Why it breaks:

SQL requires commas between column definitions.

Correct version:

CREATETABLE users (
    idINTPRIMARYKEY,
    nameVARCHAR(100)
);

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

You might try:

CREATETABLE orders (
    customer_idINT,
FOREIGNKEY (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:

priceDECIMAL(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.