Skip to content
Tutorial Reference

SQL CREATE for creating Databases and Tables

Learn how to use SQL CREATE DATABASE and CREATE TABLE statements to design relational database schemas with proper data types, constraints, and relationships for reliable data storage.

Before you can insert, query, or manipulate any data, you need a place to store it. In relational databases, that place is a table living inside a database. The CREATE statement is the SQL command that brings both of these structures into existence, defining not just their names but the exact shape of your data: which columns exist, what type of data each column holds, and what rules the data must follow.

Understanding how to write an SQL CREATE TABLE statement correctly is foundational knowledge for every developer who works with databases. A well-designed table makes queries fast, data reliable, and future changes manageable. A poorly designed one creates problems that compound over time.

This guide walks you through creating databases and tables from scratch, explains the most common data types across major database systems, covers the safety net of IF NOT EXISTS, and provides practical examples with the reasoning behind each decision.

Creating a Database

Before creating tables, you need a database to hold them. A database is a named container that groups related tables, views, indexes, and other objects together.

Basic Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE online_store;

That single line creates an empty database called online_store. It contains no tables yet, just an empty shell waiting for structure.

Using the Database

After creating a database, you need to tell your SQL client to use it. The syntax varies slightly by database system:

-- MySQL / MariaDB
USE online_store;

-- PostgreSQL (connect via psql)
\c online_store

-- SQL Server
USE online_store;

Database Creation Options

Most database systems accept additional options when creating a database:

-- MySQL: specify character set and collation
CREATE DATABASE online_store
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- PostgreSQL: specify encoding and owner
CREATE DATABASE online_store
    ENCODING 'UTF8'
    OWNER myuser;

-- SQL Server: specify file locations
CREATE DATABASE online_store
ON PRIMARY (
    NAME = 'online_store_data',
    FILENAME = 'C:\data\online_store.mdf',
    SIZE = 100MB
);

For most development scenarios, the simple CREATE DATABASE database_name is sufficient. The advanced options become relevant in production deployments.

CREATE DATABASE IF NOT EXISTS

To avoid an error when the database might already exist:

-- MySQL / MariaDB
CREATE DATABASE IF NOT EXISTS online_store;

-- PostgreSQL does NOT support IF NOT EXISTS for CREATE DATABASE directly.
-- Workaround using psql:
-- SELECT 'CREATE DATABASE online_store' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'online_store')\gexec

Creating Tables: The Foundation

Tables are where your data lives. Each table has a name, a set of columns, and rules (constraints) that enforce data integrity. The SQL CREATE TABLE statement defines all of this in one declaration.

Basic Syntax

CREATE TABLE table_name (
    column1  data_type  constraints,
    column2  data_type  constraints,
    column3  data_type  constraints
);

Each column definition has three parts:

  1. Column name: A descriptive identifier (e.g., first_name, order_date, price).
  2. Data type: What kind of data the column stores (text, numbers, dates, etc.).
  3. Constraints (optional): Rules like NOT NULL, PRIMARY KEY, DEFAULT, or UNIQUE.

Example: A Simple Customers Table

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100) UNIQUE,
    city          VARCHAR(50),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's break down every column:

Column Data Type Constraints Purpose
customer_id INT PRIMARY KEY Unique identifier for each customer
first_name VARCHAR(50) NOT NULL First name, required, max 50 characters
last_name VARCHAR(50) NOT NULL Last name, required, max 50 characters
email VARCHAR(100) UNIQUE Email, must be unique across all rows
city VARCHAR(50) (none) City, optional (allows NULL)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP Auto-set to current time on insert

Example: A Products Table

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    description   TEXT,
    category      VARCHAR(50) NOT NULL,
    price         DECIMAL(10, 2) NOT NULL,
    in_stock      BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The DECIMAL(10, 2) type means the price can have up to 10 total digits, with 2 after the decimal point. This allows values from -99999999.99 to 99999999.99. The TEXT type for description allows long-form text without a length limit.

Example: An Orders Table with a Foreign Key

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_date    DATE DEFAULT CURRENT_DATE,
    status        VARCHAR(20) DEFAULT 'pending',
    total_amount  DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The FOREIGN KEY constraint establishes a relationship: every customer_id in the orders table must match an existing customer_id in the customers table. If you try to insert an order with a customer_id that does not exist in the customers table, the database will reject the insert.

-- This works: customer 1 exists
INSERT INTO customers (customer_id, first_name, last_name) VALUES (1, 'Alice', 'Martin');
INSERT INTO orders (order_id, customer_id, total_amount) VALUES (1001, 1, 150.00);

-- This fails: customer 999 does not exist
INSERT INTO orders (order_id, customer_id, total_amount) VALUES (1002, 999, 75.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint

Common Data Types by RDBMS

Data types define what kind of values a column can store. While the core concepts are consistent across databases, the exact type names and capabilities vary. This section covers the most commonly used types and their equivalents across major systems.

Numeric Types

Category MySQL PostgreSQL SQL Server Oracle SQLite
Small integer TINYINT, SMALLINT SMALLINT TINYINT, SMALLINT NUMBER(5) INTEGER
Standard integer INT INTEGER INT NUMBER(10) INTEGER
Large integer BIGINT BIGINT BIGINT NUMBER(19) INTEGER
Exact decimal DECIMAL(p,s) DECIMAL(p,s) / NUMERIC(p,s) DECIMAL(p,s) NUMBER(p,s) REAL
Floating point FLOAT, DOUBLE REAL, DOUBLE PRECISION FLOAT, REAL BINARY_FLOAT, BINARY_DOUBLE REAL
Auto-increment INT AUTO_INCREMENT SERIAL / GENERATED ALWAYS AS IDENTITY INT IDENTITY(1,1) GENERATED ALWAYS AS IDENTITY INTEGER PRIMARY KEY AUTOINCREMENT

When to use what:

  • Use INT or BIGINT for IDs, counts, and whole numbers.
  • Use DECIMAL(p,s) for money and exact decimal values. Never use FLOAT for money because floating-point arithmetic introduces rounding errors.
  • Use FLOAT or DOUBLE only for scientific measurements where slight imprecision is acceptable.
-- Correct: exact decimal for prices
price DECIMAL(10, 2)    -- up to 99,999,999.99

-- Wrong: floating point for prices
price FLOAT             -- 19.99 might be stored as 19.989999...

String / Text Types

Category MySQL PostgreSQL SQL Server Oracle SQLite
Fixed-length CHAR(n) CHAR(n) CHAR(n) CHAR(n) TEXT
Variable-length VARCHAR(n) VARCHAR(n) VARCHAR(n) / NVARCHAR(n) VARCHAR2(n) TEXT
Unlimited text TEXT / LONGTEXT TEXT VARCHAR(MAX) / NVARCHAR(MAX) CLOB TEXT

When to use what:

  • Use VARCHAR(n) for most string columns. The n is the maximum length. Choose a reasonable limit: 50 for names, 100 for emails, 255 for short descriptions.
  • Use CHAR(n) only for fixed-length data like country codes (CHAR(2)), currency codes (CHAR(3)), or state abbreviations (CHAR(2)). It pads shorter values with spaces.
  • Use TEXT for long-form content like blog posts, descriptions, or comments where you do not want to impose a length limit.
-- Good choices
first_name    VARCHAR(50),      -- names rarely exceed 50 characters
country_code  CHAR(2),          -- always exactly 2 characters: 'US', 'DE', 'JP'
bio           TEXT,             -- no length limit for user bios
email         VARCHAR(254),     -- max valid email length per RFC 5321

Date and Time Types

Category MySQL PostgreSQL SQL Server Oracle SQLite
Date only DATE DATE DATE DATE TEXT (stored as string)
Time only TIME TIME TIME INTERVAL DAY TO SECOND TEXT
Date + time DATETIME TIMESTAMP DATETIME / DATETIME2 TIMESTAMP TEXT
With timezone TIMESTAMP (UTC conversion) TIMESTAMPTZ DATETIMEOFFSET TIMESTAMP WITH TIME ZONE TEXT

When to use what:

  • Use DATE for dates without time: birthdays, order dates, due dates.
  • Use TIMESTAMP (or DATETIME) for precise moments: creation times, last login, event logs.
  • Use timezone-aware types (TIMESTAMPTZ, DATETIMEOFFSET) when your application serves users across multiple time zones.
-- Date columns
birth_date     DATE,                    -- '1990-05-15'
order_date     DATE DEFAULT CURRENT_DATE,

-- Timestamp columns
created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login     TIMESTAMP,

-- PostgreSQL with timezone
event_time     TIMESTAMPTZ DEFAULT NOW()

Boolean Type

MySQL PostgreSQL SQL Server Oracle SQLite
BOOLEAN / TINYINT(1) BOOLEAN BIT NUMBER(1) or CHAR(1) INTEGER (0/1) 
-- PostgreSQL
is_active    BOOLEAN DEFAULT TRUE,

-- MySQL (BOOLEAN is an alias for TINYINT(1))
is_active    BOOLEAN DEFAULT TRUE,

-- SQL Server
is_active    BIT DEFAULT 1,

-- Oracle (no native boolean for tables)
is_active    NUMBER(1) DEFAULT 1 CHECK (is_active IN (0, 1))

Binary / Large Object Types

Category MySQL PostgreSQL SQL Server Oracle
Binary data BLOB / LONGBLOB BYTEA VARBINARY(MAX) BLOB
JSON JSON JSON / JSONB NVARCHAR(MAX) CLOB (21c+ has JSON)
UUID CHAR(36) / BINARY(16) UUID UNIQUEIDENTIFIER RAW(16)

Column Constraints

Constraints enforce rules on what data a column can contain. You have already seen some of these in the examples above, but let's cover each one systematically.

PRIMARY KEY

Uniquely identifies each row. A table can have only one primary key, but it can span multiple columns (composite key).

-- Single-column primary key
CREATE TABLE users (
    user_id  INT PRIMARY KEY,
    username VARCHAR(50)
);

-- Composite primary key (defined as a table constraint)
CREATE TABLE order_items (
    order_id    INT,
    product_id  INT,
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

NOT NULL

Prevents the column from storing NULL values. Every insert must provide a value (or the column must have a DEFAULT).

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,   -- required
    middle_name   VARCHAR(50),            -- optional (allows NULL)
    last_name     VARCHAR(50) NOT NULL    -- required
);

UNIQUE

Ensures no two rows have the same value in this column. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns allow NULL (in most databases, multiple NULLs are permitted).

CREATE TABLE users (
    user_id   INT PRIMARY KEY,
    username  VARCHAR(50) UNIQUE NOT NULL,
    email     VARCHAR(100) UNIQUE NOT NULL
);

DEFAULT

Provides a value when one is not specified during an insert.

CREATE TABLE tasks (
    task_id      INT PRIMARY KEY,
    title        VARCHAR(100) NOT NULL,
    status       VARCHAR(20) DEFAULT 'todo',
    priority     INT DEFAULT 0,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert without specifying status, priority, or created_at
INSERT INTO tasks (task_id, title) VALUES (1, 'Write documentation');
-- Result: status = 'todo', priority = 0, created_at = current timestamp

CHECK

Validates that column values meet a specific condition. Supported in all major databases (MySQL 8.0.16+).

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    price         DECIMAL(10, 2) CHECK (price >= 0),
    quantity      INT CHECK (quantity >= 0),
    rating        DECIMAL(2, 1) CHECK (rating BETWEEN 0.0 AND 5.0)
);
-- This succeeds
INSERT INTO products VALUES (1, 'Mouse', 25.99, 100, 4.5);

-- This fails
INSERT INTO products VALUES (2, 'Cable', -5.00, 50, 3.0);
-- ERROR: new row violates check constraint "products_price_check"

FOREIGN KEY

Creates a relationship between tables by requiring that values in one column match values in another table's column.

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_date    DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The ON DELETE and ON UPDATE clauses define what happens when the referenced row is deleted or updated:

Action Behavior
CASCADE Automatically delete/update child rows
SET NULL Set the foreign key column to NULL
SET DEFAULT Set the foreign key column to its default value
RESTRICT / NO ACTION Block the delete/update (default behavior) 
-- ON DELETE CASCADE: deleting a customer also deletes their orders
-- ON DELETE SET NULL: deleting a customer sets order.customer_id to NULL
-- ON DELETE RESTRICT: cannot delete a customer who has orders (default)

Combining Multiple Constraints

A single column can have multiple constraints:

CREATE TABLE accounts (
    account_id   INT PRIMARY KEY,
    email        VARCHAR(100) NOT NULL UNIQUE,
    balance      DECIMAL(12, 2) NOT NULL DEFAULT 0.00 CHECK (balance >= 0),
    status       VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')),
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Auto-Increment / Serial Columns

Most tables need an automatically generated unique identifier. The syntax differs significantly across databases.

PostgreSQL

-- Modern approach (PostgreSQL 10+): GENERATED AS IDENTITY
CREATE TABLE customers (
    customer_id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL
);

-- Older approach: SERIAL (still widely used)
CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL
);

MySQL

CREATE TABLE customers (
    customer_id   INT AUTO_INCREMENT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL
);

SQL Server

CREATE TABLE customers (
    customer_id   INT IDENTITY(1,1) PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL
);
-- IDENTITY(1,1) means: start at 1, increment by 1

Oracle

-- Oracle 12c+
CREATE TABLE customers (
    customer_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name    VARCHAR2(50) NOT NULL,
    last_name     VARCHAR2(50) NOT NULL
);

SQLite

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name    TEXT NOT NULL,
    last_name     TEXT NOT NULL
);

IF NOT EXISTS: Safe Table Creation

In scripts, migrations, and application startup code, you often need to create a table only if it does not already exist. Without IF NOT EXISTS, running CREATE TABLE on an existing table produces an error.

The Problem

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50)
);

-- Run the same statement again:
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50)
);
-- ERROR: relation "customers" already exists

The Solution

CREATE TABLE IF NOT EXISTS customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50)
);

If the table already exists, the statement completes silently without error. If it does not exist, the table is created normally.

Database Support

Database CREATE TABLE IF NOT EXISTS CREATE DATABASE IF NOT EXISTS
PostgreSQL Yes No
MySQL Yes Yes
SQL Server No (use conditional check) No (use conditional check)
Oracle No (use exception handling) N/A
SQLite Yes N/A

SQL Server Workaround

SQL Server does not support IF NOT EXISTS syntax directly. Use a conditional check instead:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'customers')
BEGIN
    CREATE TABLE customers (
        customer_id   INT IDENTITY(1,1) PRIMARY KEY,
        first_name    VARCHAR(50) NOT NULL,
        last_name     VARCHAR(50) NOT NULL
    );
END;

Oracle Workaround

Oracle requires PL/SQL exception handling:

BEGIN
    EXECUTE IMMEDIATE '
        CREATE TABLE customers (
            customer_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            first_name    VARCHAR2(50) NOT NULL,
            last_name     VARCHAR2(50) NOT NULL
        )
    ';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -955 THEN NULL;  -- Table already exists
        ELSE RAISE;
        END IF;
END;
/

Putting It All Together: A Complete Schema

Here is a complete e-commerce schema that demonstrates all the concepts covered in this guide:

-- Create the database
CREATE DATABASE IF NOT EXISTS online_store;

-- Products table
CREATE TABLE IF NOT EXISTS products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    description   TEXT,
    category      VARCHAR(50) NOT NULL,
    price         DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_qty     INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers table
CREATE TABLE IF NOT EXISTS customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(254) UNIQUE NOT NULL,
    phone         VARCHAR(20),
    city          VARCHAR(50),
    country       CHAR(2) DEFAULT 'US',
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    status        VARCHAR(20) NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending', 'processing', 'shipped', 'completed', 'cancelled')),
    total_amount  DECIMAL(10, 2) CHECK (total_amount >= 0),
    notes         TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

-- Order items (junction table with composite primary key)
CREATE TABLE IF NOT EXISTS order_items (
    order_id      INT NOT NULL,
    product_id    INT NOT NULL,
    quantity      INT NOT NULL CHECK (quantity > 0),
    unit_price    DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

This schema demonstrates:

  • Appropriate data types for each column (INT for IDs, VARCHAR with sensible limits for strings, DECIMAL for money, DATE for dates, BOOLEAN for flags).
  • NOT NULL on required columns, nullable columns where data may be absent.
  • DEFAULT values for status fields, timestamps, and country codes.
  • CHECK constraints to prevent invalid data (negative prices, zero quantities, invalid statuses).
  • UNIQUE on email to prevent duplicate accounts.
  • FOREIGN KEY relationships with explicit cascade rules.
  • Composite primary key on the junction table order_items.
  • IF NOT EXISTS for safe re-running of the script.

Naming Conventions

Good table and column names make your schema self-documenting. While conventions vary by team, here are widely accepted practices:

-- Good: descriptive, lowercase, underscores
CREATE TABLE order_items (
    item_id       INT PRIMARY KEY,
    order_id      INT NOT NULL,
    product_id    INT NOT NULL,
    unit_price    DECIMAL(10, 2) NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Bad: inconsistent casing, abbreviations, unclear names
CREATE TABLE OrderItm (
    oi_id      INT PRIMARY KEY,
    oid        INT NOT NULL,
    pid        INT NOT NULL,
    up         DECIMAL(10, 2) NOT NULL,
    ca         TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Common Mistakes

Mistake 1: Choosing VARCHAR Without Thinking About Length

Setting every VARCHAR to the maximum allowed length wastes nothing in storage (most databases only store actual data), but it removes a useful validation layer.

Careless:

CREATE TABLE users (
    first_name  VARCHAR(10000),   -- nobody has a 10,000-character first name
    email       VARCHAR(10000),
    phone       VARCHAR(10000)
);

Thoughtful:

CREATE TABLE users (
    first_name  VARCHAR(50),     -- reasonable max for a first name
    email       VARCHAR(254),    -- RFC 5321 maximum email length
    phone       VARCHAR(20)      -- international format with country code
);

The length limit acts as a sanity check. If someone accidentally tries to insert a 5,000-character string as a phone number, the database catches the error instead of silently storing garbage data.

Mistake 2: Using FLOAT for Money

Already covered in the data types section, but it deserves emphasis:

Wrong:

CREATE TABLE invoices (
    amount  FLOAT    -- rounding errors will corrupt financial data
);

Correct:

CREATE TABLE invoices (
    amount  DECIMAL(12, 2)    -- exact decimal arithmetic
);

Mistake 3: Missing NOT NULL on Required Columns

If a column should always have a value, enforce it at the database level. Relying on application code alone leaves gaps.

Risky:

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,              -- allows NULL: orphan orders possible
    order_date    DATE,             -- allows NULL: undated orders possible
    total_amount  DECIMAL(10, 2)
);

Safe:

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,     -- every order must belong to a customer
    order_date    DATE NOT NULL DEFAULT CURRENT_DATE,  -- always has a date
    total_amount  DECIMAL(10, 2)    -- NULL is OK here: total might be calculated later
);

Mistake 4: Forgetting Foreign Keys

Without foreign keys, nothing prevents invalid references. Your orders table could reference customer ID 9999 even if no such customer exists.

No referential integrity:

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL
    -- no FOREIGN KEY: any integer is accepted
);

With referential integrity:

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Mistake 5: Creating Tables in the Wrong Order

Foreign keys reference other tables, so those tables must exist first. The order of CREATE TABLE statements matters.

Wrong order (fails):

-- orders references customers, but customers doesn't exist yet
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)   -- ERROR!
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50)
);
ERROR: relation "customers" does not exist

Correct order:

-- Create referenced tables first
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50)
);

-- Then create tables that reference them
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

Viewing Table Structure

After creating a table, you can inspect its structure:

-- PostgreSQL
\d customers

-- MySQL
DESCRIBE customers;
-- or
SHOW CREATE TABLE customers;

-- SQL Server
EXEC sp_columns 'customers';
-- or
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'customers';

-- SQLite
.schema customers

Summary

The SQL CREATE TABLE statement is the foundation of every database schema. It defines the structure that all of your data will follow, from column names and data types to the constraints that ensure data integrity.

Key takeaways:

  • CREATE DATABASE creates a named container for your tables. Use IF NOT EXISTS (where supported) to avoid errors in scripts.
  • CREATE TABLE defines columns, data types, and constraints. Each column needs a name, a type, and optionally one or more constraints.
  • Choose data types carefully. Use INT or BIGINT for whole numbers, DECIMAL for money (never FLOAT), VARCHAR(n) for variable-length strings with a reasonable limit, DATE for dates, and TIMESTAMP for precise moments.
  • Data type names vary by database. Oracle uses VARCHAR2 and NUMBER, SQL Server uses IDENTITY for auto-increment, PostgreSQL uses SERIAL or GENERATED AS IDENTITY, and MySQL uses AUTO_INCREMENT.
  • Constraints enforce data quality at the database level. Use NOT NULL for required columns, UNIQUE for columns that must have distinct values, CHECK for value validation, DEFAULT for automatic values, and FOREIGN KEY for referential integrity.
  • IF NOT EXISTS prevents errors when a table might already exist, making your creation scripts safe to re-run. Not all databases support this syntax natively; SQL Server and Oracle require conditional workarounds.
  • Table creation order matters when foreign keys are involved. Create referenced tables before the tables that reference them.
  • Name things clearly. Use descriptive, lowercase names with underscores. Good naming makes your schema readable without documentation.

A well-designed table is the single best investment you can make in a database project. The few minutes spent choosing the right types, constraints, and names save hours of debugging, data cleanup, and migration work down the road.

Navigation