SQL CREATE TABLE: Complete Guide with Interactive Examples

The SQL CREATE TABLE statement is the foundation of database design. Every time you build an application that stores data, you’ll use CREATE TABLE to define the structure of your database tables.

Throughout this guide, you’ll find interactive SQL exercises that let you practice CREATE TABLE in your browser—no database setup required. Write queries, get instant feedback, and see if your results match the expected output.

Understanding CREATE TABLE is essential whether you’re building a new application, learning SQL, or maintaining existing databases. It defines not just what data you can store, but how that data is validated and protected.

Basic CREATE TABLE Syntax

The CREATE TABLE statement defines a new table with columns and their data types:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Each column needs a name and a data type. The data type tells the database what kind of values the column can hold.

Here’s a simple example creating a table to store book information:

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT,
    price DECIMAL(10,2),
    published_date DATE
);

This creates a table with five columns, each with an appropriate data type for the data it will store.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Common SQL Data Types

Choosing the right data type is important for data integrity and storage efficiency. Here are the most commonly used types:

Numeric Types

• INTEGER (or INT) - Whole numbers: 1, 42, -100
• DECIMAL(p,s) - Exact decimal numbers with precision p and scale s
• FLOAT/REAL - Approximate decimal numbers for scientific calculations

Text Types

• TEXT - Variable-length strings of any size
• VARCHAR(n) - Variable-length string with maximum length n
• CHAR(n) - Fixed-length string, padded with spaces

Date and Time Types

• DATE - Calendar date (year, month, day)
• TIME - Time of day
• TIMESTAMP - Date and time combined
• DATETIME - Date and time (used in some databases instead of TIMESTAMP)

Other Types

• BOOLEAN - True/false values
• BLOB - Binary data (images, files)

Note: Data type names and features vary slightly between database systems. SQLite is particularly flexible with types, while PostgreSQL and MySQL enforce them more strictly.

Primary Keys

A primary key uniquely identifies each row in a table. It’s a fundamental concept in database design:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

Primary key characteristics:

• Unique: No two rows can have the same primary key value
• Not NULL: Primary key columns cannot contain NULL values
• Single purpose: Identifies exactly one row

Many databases support auto-incrementing primary keys:

-- PostgreSQL
customer_id SERIAL PRIMARY KEY

-- MySQL
customer_id INT AUTO_INCREMENT PRIMARY KEY

-- SQLite
customer_id INTEGER PRIMARY KEY  -- Auto-increments automatically

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Column Constraints

Constraints are rules that enforce data integrity. They prevent invalid data from being inserted into your tables.

NOT NULL

Requires a value—NULL is not allowed:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

DEFAULT

Provides a value when none is specified:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

UNIQUE

Prevents duplicate values in a column:

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username TEXT UNIQUE
);

CHECK

Validates that values meet a condition:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    price DECIMAL CHECK(price > 0),
    quantity INTEGER CHECK(quantity >= 0)
);

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Combining Multiple Constraints

You can apply multiple constraints to a single column:

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    salary DECIMAL NOT NULL CHECK(salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE
);

The order of constraints doesn’t matter—they all apply equally. This employee table ensures:

• Each employee has a unique ID
• Email addresses are required and unique
• Salaries must be positive numbers
• Hire date defaults to today if not specified

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Foreign Keys

Foreign keys create relationships between tables by referencing the primary key of another table:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Foreign keys enforce referential integrity:

• You cannot insert an order with a customer_id that doesn’t exist in the customers table
• You cannot delete a customer who has orders (unless you use CASCADE)

Foreign Key Actions

Specify what happens when referenced data changes:

CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

• CASCADE - Delete/update related rows automatically
• RESTRICT - Prevent deletion if related rows exist
• SET NULL - Set the foreign key to NULL when parent is deleted
• SET DEFAULT - Set to default value when parent is deleted

Table-Level Constraints

Some constraints apply to the entire table rather than individual columns:

Composite Primary Key

When a single column isn’t enough to uniquely identify rows:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE,
    grade TEXT,
    PRIMARY KEY (student_id, course_id)
);

Named Constraints

Naming constraints makes error messages clearer and allows you to modify them later:

CREATE TABLE products (
    product_id INTEGER,
    name TEXT NOT NULL,
    price DECIMAL,
    CONSTRAINT pk_products PRIMARY KEY (product_id),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT uq_name UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS

Prevent errors when a table might already exist:

CREATE TABLE IF NOT EXISTS logs (
    log_id INTEGER PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This is especially useful in:

• Database migration scripts
• Application initialization code
• Development environments

CREATE TABLE AS SELECT

Create a new table from the results of a query:

CREATE TABLE active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE last_order_date > '2024-01-01';

This copies both the structure and data. The new table is independent—changes to the original don’t affect it.

Note: Constraints from the original table are not copied. You’ll need to add them separately if needed.

Temporary Tables

Create tables that exist only for the current session:

CREATE TEMPORARY TABLE cart_items (
    item_id INTEGER PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER
);

Temporary tables are useful for:

• Storing intermediate results in complex queries
• Session-specific data in web applications
• Testing and development

Best Practices for CREATE TABLE

Choose Descriptive Names

Use clear, consistent naming:

-- Good
CREATE TABLE customer_orders (...)
CREATE TABLE product_categories (...)

-- Avoid
CREATE TABLE tbl1 (...)
CREATE TABLE data (...)

Use Appropriate Data Types

Don’t use TEXT for everything:

-- Good
price DECIMAL(10,2)
quantity INTEGER
is_active BOOLEAN

-- Less efficient
price TEXT
quantity TEXT
is_active TEXT

Add Constraints Early

Define constraints when creating the table, not later:

-- Better: Constraints defined upfront
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Document with Comments

Many databases support comments on tables and columns:

-- PostgreSQL
COMMENT ON TABLE customers IS 'Stores customer contact information';
COMMENT ON COLUMN customers.email IS 'Primary contact email address';

Working with CREATE TABLE in Beekeeper Studio

Creating and managing tables is much easier with a visual tool. Beekeeper Studio provides intuitive table management:

Features that help with table creation:

• Visual table designer: Create tables without writing SQL
• Column editor: Define data types and constraints visually
• Relationship viewer: See foreign key connections between tables
• SQL preview: Review the generated CREATE TABLE statement before executing
• Schema browser: Navigate existing tables for reference

The free version includes table creation tools for PostgreSQL, MySQL, SQLite, SQL Server, and many more databases.

Common Errors and Solutions

Column Already Exists

Error: duplicate column name

Check your CREATE TABLE statement for repeated column names.

Table Already Exists

Error: table already exists

Use CREATE TABLE IF NOT EXISTS or DROP TABLE IF EXISTS first.

Foreign Key Constraint Failed

Error: FOREIGN KEY constraint failed

Ensure the referenced table and column exist, and that you’re inserting valid reference values.

Invalid Default Value

Error: Invalid default value for column

Make sure the default value matches the column’s data type.

Key Takeaways

The CREATE TABLE statement is fundamental to SQL and database design:

• Define structure: Specify columns and their data types to organize your data
• Enforce integrity: Use constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK) to validate data
• Build relationships: Foreign keys connect tables and maintain referential integrity
• Choose appropriate types: Match data types to the actual data you’ll store
• Plan ahead: Add constraints when creating tables, not as an afterthought

Understanding CREATE TABLE thoroughly will help you design databases that are reliable, efficient, and maintainable. Practice with the interactive examples above to build your confidence.

Ready to learn more? Explore our guides on SQL GROUP BY and SQL ORDER BY to continue building your SQL skills.