SQL CREATE for creating Databases and Tables

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;

info

In PostgreSQL, you typically create databases using the createdb command-line tool or the CREATE DATABASE SQL statement, but you cannot switch databases within the same session using USE. You must disconnect and reconnect to the new database. Most PostgreSQL GUI tools and drivers handle this automatically when you select a database.

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

warning

PostgreSQL does not support IF NOT EXISTS on CREATE DATABASE. If you need this behavior, check for the database's existence in your application code or shell script before issuing the command. SQL Server also lacks this syntax natively and requires a conditional check with IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'online_store').

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

danger

Never use FLOAT or DOUBLE for monetary values. Floating-point types store approximations, not exact values. A product priced at 19.99 might be stored internally as 19.9899999999999984 and calculations will accumulate rounding errors. Always use DECIMAL or NUMERIC for money.

-- Demonstrating floating-point imprecision
SELECT CAST(19.99 AS FLOAT) + CAST(0.01 AS FLOAT);
-- May return: 20.000000000000004 instead of 20.00

SELECT CAST(19.99 AS DECIMAL(10,2)) + CAST(0.01 AS DECIMAL(10,2));
-- Returns exactly: 20.00

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

tip

Oracle uses VARCHAR2 instead of VARCHAR. While Oracle technically supports VARCHAR, its behavior may change in future versions. Always use VARCHAR2 in Oracle databases.

-- Oracle
CREATE TABLE customers (
    first_name  VARCHAR2(50),
    email       VARCHAR2(254)
);

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()

info

SQLite does not have dedicated date/time types. Dates and timestamps are stored as TEXT (ISO 8601 strings like '2024-06-15 14:30:00'), REAL (Julian day numbers), or INTEGER (Unix timestamps). SQLite's built-in date functions work with all three formats.

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)

tip

PostgreSQL's JSONB type is particularly powerful. It stores JSON in a binary format that supports indexing, allowing fast queries on JSON fields:

-- PostgreSQL
CREATE TABLE events (
    event_id   SERIAL PRIMARY KEY,
    payload    JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSON data
INSERT INTO events (payload)
VALUES ('{"type": "click", "page": "/home", "user_id": 42}');

-- Query JSON fields
SELECT * FROM events WHERE payload->>'type' = 'click';

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
);

tip

When using auto-increment columns, omit them from your INSERT statements and let the database generate the value:

-- Correct: let the database assign the ID
INSERT INTO customers (first_name, last_name) VALUES ('Alice', 'Martin');

-- Generally avoid: manually specifying the auto-generated ID
INSERT INTO customers (customer_id, first_name, last_name) VALUES (1, 'Alice', 'Martin');

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;
/

info

IF NOT EXISTS checks for the table name only. It does not verify that the existing table has the same structure as the one you are trying to create. If a table named customers exists with different columns, IF NOT EXISTS will skip the creation silently, and your code will work with the old structure.

-- Existing table has 2 columns
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50));

-- This does NOT update the table. It does nothing because "customers" already exists.
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)    -- this column will NOT be added
);

To modify an existing table's structure, use ALTER TABLE instead.

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
);

tip

Follow these naming guidelines for readable schemas:

• Table names: plural nouns, lowercase with underscores (customers, order_items, product_categories).
• Column names: singular, descriptive, lowercase with underscores (first_name, order_date, total_amount).
• Primary keys: table_singular_id or just id (customer_id, product_id).
• Foreign keys: match the referenced column name (customer_id in orders matches customer_id in customers).
• Boolean columns: prefix with is_ or has_ (is_active, has_discount, is_deleted).
• Timestamps: suffix with _at (created_at, updated_at, deleted_at).

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)
);

tip

When designing a schema with multiple related tables, draw out the relationships first, then create tables in dependency order: tables with no foreign keys first, then tables that reference them, and so on. The reverse order applies when dropping tables.

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.