SQL CREATE TABLE — FLOAT Breaks $49.99 Summations

Every app you use stores data somewhere. Instagram stores your username, follower count, and the exact timestamp of every post. Your bank stores your account number, balance, and transaction history. That data doesn't just float around freely — it lives in structured tables inside a database, and someone had to design those tables before a single row of data was ever saved. That someone could be you, and the tool for the job is SQL's CREATE TABLE statement.

Before CREATE TABLE existed, developers had to manage data in flat files — think giant text documents where structure was implied but never enforced. Nothing stopped someone from saving the word 'banana' into a field meant to hold a bank balance. CREATE TABLE solves that by letting you declare a blueprint upfront: here are the columns, here is what type of data each column accepts, and here are the rules that every single row must follow, no exceptions.

By the end of this article you'll be able to write a complete CREATE TABLE statement from scratch, choose the right data type for any column, add constraints that protect your data integrity, and avoid the three mistakes that trip up almost every beginner. You'll also walk away knowing how to answer the CREATE TABLE questions that come up in real SQL interviews.

What a Table Actually Is — and Why You Design It First

A database table is a grid of rows and columns, almost identical to a spreadsheet tab. Each column has a name and a fixed data type. Each row is one record — one user, one order, one product. The critical difference from a spreadsheet is that you lock down the structure before any data goes in. You can't just add a random column mid-row the way you might type anywhere in Excel.

This design-first approach is what makes relational databases so reliable. When your app inserts a new user record, the database already knows exactly what shape that record must have. It rejects anything that doesn't fit — a text value in a numeric column, a date that doesn't exist, a username that's too long. That enforcement happens automatically, 24/7, without any code on your part.

Think of it like designing a parking garage before you open it. You decide the width of each space (data type), whether a space is reserved (constraints), and how many spaces exist (columns). You can't add a half-space later without a redesign. SQL works the same way — invest in the design upfront and the database protects your data forever.

The CREATE TABLE statement is literally you handing that blueprint to the database engine and saying: 'Build this structure and enforce these rules from now on.'

-- Step 1: Create a simple users table for a social media app
-- This is the most basic form of CREATE TABLE — no constraints yet,
-- just column names and data types. We'll add constraints in the next section.

CREATE TABLE users (
    user_id       INT,           -- A whole number to uniquely identify each user
    username      VARCHAR(50),   -- Variable-length text, max 50 characters
    email         VARCHAR(100),  -- Emails can be longer, so we allow up to 100 chars
    age           INT,           -- Whole number — ages don't have decimal points
    profile_bio   TEXT,          -- Unlimited text for longer content like bios
    created_at    DATETIME,      -- Stores both the date AND the time of registration
    is_verified   BOOLEAN        -- TRUE or FALSE — is this account email-verified?
);

-- To confirm the table was created, run:
DESCRIBE users;  -- MySQL syntax
-- or
-- \d users       -- PostgreSQL syntax

SQL Data Types Explained — Choosing the Right One Every Time

Choosing a data type is one of the most consequential decisions you make when designing a table. Get it wrong and you either waste storage, break functionality, or both. Think of data types as choosing the right container in a kitchen: you wouldn't store soup in a paper bag or carry sand in a colander. The container has to match what's going in.

SQL data types fall into four families: Numbers, Text, Dates/Times, and Boolean. Within each family there are options that trade off range versus storage size.

Numbers: Use INT for whole numbers (user IDs, quantities, ages). Use DECIMAL(precision, scale) for money — never use FLOAT for currency because floating-point arithmetic introduces tiny rounding errors that will cost real money. DECIMAL(10, 2) means up to 10 total digits, with exactly 2 after the decimal point — perfect for prices like 1299.99.

Text: VARCHAR(n) is your default for text — it only uses as much space as the actual string, up to n characters. CHAR(n) always uses exactly n characters (padded with spaces) and is faster for fixed-length values like country codes ('US', 'UK'). TEXT holds unlimited text and is ideal for blog posts or descriptions.

Dates: DATE stores only the calendar date. TIME stores only the clock time. DATETIME stores both. TIMESTAMP also stores both but automatically converts to UTC — useful for apps with global users.

Boolean: BOOLEAN (or TINYINT(1) in MySQL) stores TRUE/FALSE — perfect for flags like is_active or is_verified.

-- A real e-commerce products table showing deliberate data type choices.
-- Read the inline comments to understand WHY each type was chosen.

CREATE TABLE products (
    product_id      INT,                  -- Whole number ID — products don't have fractional IDs
    product_name    VARCHAR(150),         -- Names vary in length; VARCHAR saves storage vs CHAR
    sku_code        CHAR(10),             -- SKUs are always exactly 10 chars — CHAR is faster here
    description     TEXT,                 -- Product descriptions can be long — no character limit needed
    price           DECIMAL(10, 2),       -- CRITICAL: use DECIMAL for money, never FLOAT
                                          -- DECIMAL(10,2) = up to 10 digits, always 2 decimal places
                                          -- e.g. 99999999.99 is the max value here
    stock_quantity  INT,                  -- You can't have 2.5 items in stock — whole number only
    weight_kg       DECIMAL(6, 3),        -- Weight needs decimals: 1.250 kg is valid
    is_available    BOOLEAN,              -- Simple flag: is the product currently for sale?
    listed_on       DATE,                 -- We only care about the calendar date, not the time
    last_updated    TIMESTAMP             -- Auto-tracks date+time; great for audit trails
);

-- Let's also look at what FLOAT looks like to understand why we avoid it for money:
-- SELECT 0.1 + 0.2;  -- In many systems this returns 0.30000000000000004 — not 0.30!
-- DECIMAL avoids this entirely. Always use DECIMAL for financial values.

DESCRIBE products;

Constraints — The Rules That Make Your Data Trustworthy

A table without constraints is just a suggestion. You could insert a user with a NULL email address, a negative price, or two products with the same ID. Constraints are the rules you bolt onto columns (or the whole table) that the database enforces automatically, on every single INSERT and UPDATE, forever.

Here are the six constraints you'll use constantly:

PRIMARY KEY — Uniquely identifies each row. No two rows can share the same value, and it can never be NULL. Every table should have one. Combine with AUTO_INCREMENT (MySQL) or SERIAL (PostgreSQL) so the database generates the ID for you.

NOT NULL — This column must always have a value. NULL is not allowed. Use it on any column that genuinely must exist for every record — like email or username.

UNIQUE — Values in this column must be unique across all rows, but unlike PRIMARY KEY, a UNIQUE column can contain NULLs. Use it for things like email addresses or usernames — no two users should share one.

DEFAULT — If no value is provided during INSERT, use this fallback. Great for created_at timestamps or boolean flags that default to FALSE.

CHECK — Define a custom rule. CHECK (age >= 18) will reject any row where age is below 18. The database enforces it, not your application code.

FOREIGN KEY — Links a column in this table to the PRIMARY KEY of another table, enforcing referential integrity. You can't insert an order for a user_id that doesn't exist in the users table.

-- First, we need a users table to reference from orders.
-- Notice how we apply constraints at the column level — clean and readable.

CREATE TABLE users (
    user_id     INT           AUTO_INCREMENT PRIMARY KEY,  -- AUTO_INCREMENT: DB generates 1, 2, 3...
                                                           -- PRIMARY KEY: unique + never NULL
    username    VARCHAR(50)   NOT NULL UNIQUE,             -- Must exist, must be unique per user
    email       VARCHAR(100)  NOT NULL UNIQUE,             -- Every user needs a unique email
    age         INT           CHECK (age >= 13),           -- Reject anyone under 13 (e.g. COPPA compliance)
    country     CHAR(2)       DEFAULT 'US',                -- If not specified, assume USA
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP    -- Auto-fills with NOW() on every INSERT
);

-- Now create an orders table that references users.
-- A FOREIGN KEY means: every order must belong to a real, existing user.

CREATE TABLE orders (
    order_id      INT           AUTO_INCREMENT PRIMARY KEY,
    user_id       INT           NOT NULL,                   -- Cannot be NULL — every order needs an owner
    order_total   DECIMAL(10,2) NOT NULL CHECK (order_total > 0),  -- Must be positive
    order_status  VARCHAR(20)   NOT NULL DEFAULT 'pending', -- Sensible default when order is first created
    placed_at     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    -- FOREIGN KEY defined at the bottom — links orders.user_id to users.user_id
    -- ON DELETE CASCADE means: if the user is deleted, their orders are deleted too
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
        ON DELETE CASCADE
);

-- Test: try inserting a valid user and order
INSERT INTO users (username, email, age) VALUES ('maya_dev', 'maya@example.com', 25);
-- user_id = 1 is auto-generated, country = 'US' by default, created_at = now

INSERT INTO orders (user_id, order_total) VALUES (1, 149.99);
-- order_id = 1, status = 'pending' by default, placed_at = now

-- Test: try inserting an order for a non-existent user
INSERT INTO orders (user_id, order_total) VALUES (9999, 50.00);
-- This WILL FAIL — user_id 9999 doesn't exist in users table

Defensive Table Creation — IF NOT EXISTS and Real-World Patterns

In production, you rarely run CREATE TABLE just once. You run migration scripts, set up test environments, redeploy applications, and onboard new developers. If you run a CREATE TABLE statement on a database where that table already exists, you get an error that stops your entire script. That's why every professional CREATE TABLE statement includes IF NOT EXISTS.

IF NOT EXISTS tells the database: 'Create this table only if it doesn't already exist. If it does exist, skip this statement silently and move on.' This makes your setup scripts idempotent — you can run them ten times in a row and they'll only ever create the table once, without errors.

The other real-world pattern worth knowing is CREATE TABLE ... LIKE and CREATE TABLE ... AS SELECT. LIKE copies the structure of an existing table (columns, types, constraints) without any data — perfect for making a test copy. AS SELECT creates a new table and fills it with the results of a query — useful for snapshots or archiving.

Finally, always use DROP TABLE IF EXISTS before CREATE TABLE in setup scripts where you want a clean slate. The order matters: drop first, then create.

-- PATTERN 1: IF NOT EXISTS — the safe default for any script you'll run more than once
CREATE TABLE IF NOT EXISTS blog_posts (
    post_id       INT           AUTO_INCREMENT PRIMARY KEY,
    author_id     INT           NOT NULL,
    title         VARCHAR(200)  NOT NULL,
    slug          VARCHAR(200)  NOT NULL UNIQUE,   -- URL-friendly version of title, must be unique
    body          TEXT          NOT NULL,
    is_published  BOOLEAN       NOT NULL DEFAULT FALSE,  -- Drafts are unpublished by default
    published_at  DATETIME      NULL,                    -- NULL until the post is actually published
    created_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                -- ON UPDATE: automatically refreshes this column whenever the row changes

    CONSTRAINT fk_post_author
        FOREIGN KEY (author_id) REFERENCES users (user_id)
        ON DELETE RESTRICT  -- RESTRICT: prevent deleting a user who has blog posts
);

-- PATTERN 2: Clean-slate setup for development or testing
-- Drop if it exists, then create fresh. Use only in dev — never in production!
DROP TABLE IF EXISTS blog_posts;  -- Silently skips if table doesn't exist
CREATE TABLE blog_posts (
    post_id  INT AUTO_INCREMENT PRIMARY KEY,
    title    VARCHAR(200) NOT NULL
    -- ... rest of columns
);

-- PATTERN 3: Copy a table's structure for testing (no data copied)
CREATE TABLE blog_posts_test LIKE blog_posts;
-- blog_posts_test now has identical columns and constraints but zero rows

-- PATTERN 4: Create a table from a query result (useful for reporting snapshots)
CREATE TABLE published_posts_snapshot AS
    SELECT post_id, title, published_at
    FROM blog_posts
    WHERE is_published = TRUE;
-- Note: AS SELECT does NOT copy constraints like PRIMARY KEY — just structure + data

Create Table from Existing Table — Copy Structure Without the Pain

You don't always start from scratch. Sometimes you need a staging table for ETL, a backup of a production schema, or a quick sandbox. SQL lets you clone a table's structure (and optionally its data) with a single statement. But here's the trap: CREATE TABLE new_table AS SELECT * FROM old_table copies the data but often drops your indexes, constraints, and default values. You'll get a bare-bones heap of rows with no referential integrity. That's fine for temp work. Fatal if you think it's a real copy.

Use CREATE TABLE ... LIKE when you want the exact schema — keys, defaults, and all — without the rows. Or use CREATE TABLE ... AS SELECT with a WHERE 1=0 clause to get an empty structural clone. Know which one you need before you type. Production outages start with assumptions that a cloned table behaves like the original.

// io.thecodeforge — database tutorial

-- Clone structure only (indexes, defaults, constraints included)
CREATE TABLE staging_customer LIKE customer;

-- Clone structure with a subset of rows (no constraints copied)
CREATE TABLE backup_customer AS
SELECT * FROM customer
WHERE signup_date >= '2024-01-01';

-- Clone structure only, empty, using AS SELECT trick
CREATE TABLE empty_customer AS
SELECT * FROM customer
WHERE 1=0;

Inserting Data into the Table — You Built It, Now Fill It Right

You wrote the CREATE TABLE. The schema is tight. Now shove data into it. But don't just throw raw INSERT statements at production. You need patterns that handle volume, prevent duplicates, and don't lock the whole table for hours.

First rule: always list the columns explicitly. INSERT INTO customer (id, name, email) VALUES (1, 'Alice', 'a@b.com') is safe. INSERT INTO customer VALUES (1, 'Alice', 'a@b.com') breaks the moment you add a column. I've seen this take down a deployment. Second rule: batch your inserts. A single INSERT with multiple rows is faster than a loop of single-row inserts. Third rule: use INSERT ... ON CONFLICT (PostgreSQL) or INSERT ... ON DUPLICATE KEY UPDATE (MySQL) for upserts. It saves you a separate SELECT check and avoids race conditions.

Never use string concatenation to build INSERT values. That's how you get SQL injection. Use parameterised queries or prepared statements in your app layer.

// io.thecodeforge — database tutorial

-- Safe insert with explicit columns
INSERT INTO customer (customer_id, first_name, last_name, email, signup_date)
VALUES (1001, 'Maria', 'Santos', 'maria@example.com', '2025-01-15');

-- Batch insert (preferred for bulk loads)
INSERT INTO customer (customer_id, first_name, last_name, email, signup_date)
VALUES
  (1002, 'James', 'Chen', 'james@example.com', '2025-01-16'),
  (1003, 'Aiko', 'Tanaka', 'aiko@example.com', '2025-01-17'),
  (1004, 'Luca', 'Bianchi', 'luca@example.com', '2025-01-18');

-- Upsert pattern (PostgreSQL syntax)
INSERT INTO customer (customer_id, email, signup_date)
VALUES (1001, 'maria.new@example.com', '2025-01-15')
ON CONFLICT (customer_id)
DO UPDATE SET email = EXCLUDED.email;

Using the Command Line Client — No GUI Safety Net

If you only ever design tables through a GUI tool like DBeaver or MySQL Workbench, you're flying blind. The command line client is where you prove you actually understand what's happening. It strips away the auto-complete, the schema diagrams, and the pretty colors. You type SQL or you fail.

Here's the reality: production databases don't have a GUI. When you're on a server at 2AM debugging a locked table, you get a terminal and a blinking cursor. The command line client teaches you to think in statements, not clicks. It forces you to validate your syntax mentally before hitting enter. And it makes you read error messages — really read them — because there's no friendly popup.

Start using mysql -u root -p (or psql -U postgres) and write your CREATE TABLE statements by hand. It's faster once you're fluent, and it makes you a better engineer. No training wheels.

// io.thecodeforge — database tutorial

-- Connect to MySQL from terminal
mysql -u root -p

-- Output: Enter password:
-- Output: Welcome to the MySQL monitor...

USE shop_inventory;

-- Output: Database changed

CREATE TABLE IF NOT EXISTS warehouse (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    location    VARCHAR(100) NOT NULL,
    capacity    INT DEFAULT 0,
    manager_id  INT UNIQUE
);

-- Output: Query OK, 0 rows affected (0.02 sec)

SHOW TABLES;

-- Output: +-------------------------+
-- Output: | Tables_in_shop_inventory |
-- Output: +-------------------------+
-- Output: | warehouse                |
-- Output: +-------------------------+

DESC warehouse;

-- Output: +------------+--------------+------+-----+---------+----------------+
-- Output: | Field      | Type         | Null | Key | Default | Extra          |
-- Output: +------------+--------------+------+-----+---------+----------------+
-- Output: | id         | int          | NO   | PRI | NULL    | auto_increment |
-- Output: | location   | varchar(100) | NO   |     | NULL    |                |
-- Output: | capacity   | int          | YES  |     | 0       |                |
-- Output: | manager_id | int          | YES  | UNI | NULL    |                |
-- Output: +------------+--------------+------+-----+---------+----------------+

Using the Command Line Client — Row-by-Row Inserts That Don't Suck

After you CREATE TABLE from the CLI, the natural next step is populating it. And the worst thing you can do is type INSERT statements blindly. The command line client gives you immediate feedback — syntax errors, constraint violations, duplicate keys. You see them instantly, not buried in a GUI log.

Run your INSERTs line by line. It feels slow, but it's deliberate. You watch the 'Query OK, 1 row affected' and you know exactly what went in. When you hit a duplicate key error, you fix it right there. When you miss a NOT NULL column, the error is crystal clear. This is how you build muscle memory for your schema.

Batch inserts work too: INSERT INTO warehouse (location, capacity) VALUES ('NYC', 500), ('LA', 300), ('CHI', 400);. One statement, three rows, one response. But don't batch until you've validated the structure with single rows first. Trust me.

// io.thecodeforge — database tutorial

-- Single row insert
INSERT INTO warehouse (location, capacity, manager_id)
VALUES ('NYC', 500, 101);

-- Output: Query OK, 1 row affected (0.01 sec)

-- Intentional error: missing NOT NULL 'location'
INSERT INTO warehouse (capacity) VALUES (200);

-- Output: ERROR 1364 (HY000): Field 'location' doesn't have a default value

-- Batch insert — 3 rows at once
INSERT INTO warehouse (location, capacity, manager_id)
VALUES 
    ('LA', 300, 102),
    ('CHI', 400, 103),
    ('ATL', 250, 104);

-- Output: Query OK, 3 rows affected (0.01 sec)
-- Output: Records: 3  Duplicates: 0  Warnings: 0

-- Verify with a quick select
SELECT id, location, capacity FROM warehouse;

-- Output: +----+----------+----------+
-- Output: | id | location | capacity |
-- Output: +----+----------+----------+
-- Output: |  1 | NYC      |      500 |
-- Output: |  2 | LA       |      300 |
-- Output: |  3 | CHI      |      400 |
-- Output: |  4 | ATL      |      250 |
-- Output: +----+----------+----------+