Skip to content
Tutorials Center
My learning

Topics

Lesson 66 of 125 ยท Designing Databases

CREATE TABLE

Learn SQL CREATE TABLE with live examples. Define columns, types, primary keys and defaults, use IF NOT EXISTS and STRICT tables, and copy query results with CREATE TABLE AS.

Until now you've worked with tables someone else designed. From here on you'll design your own, and it starts with CREATE TABLE. The statement gives the table a name and describes every column: what it's called, what kind of data it holds, and which rules its values must follow.

Table design deserves care. Queries are easy to rewrite, but once a table holds millions of rows and a dozen applications depend on it, changing its structure is slow and risky. A well-designed table keeps bad data out, makes queries simple and performs well for years. This lesson covers the syntax and the core building blocks: columns, types, primary keys, defaults and a first look at constraints. The following lessons in this chapter go deeper into keys, data types, constraints and foreign keys.

The basic syntax

SQL
CREATE TABLE table_name (
  column_name data_type column_constraints,
  column_name data_type column_constraints,
  ...
  table_constraints
);

Each column definition has a name, a type and optionally some constraints, such as NOT NULL. Constraints that involve several columns, like a two-column primary key, go at the end as table constraints. Here's a table for a small event-booking app:

SQL Your first table

Press Run (or Ctrl + Enter) to see the result here.

CREATE TABLE reports OK.: it changes the schema, not any rows. The table then works like any other: INSERT fills it, and id numbers itself because it's an INTEGER PRIMARY KEY.

Naming tables and columns

Names are forever, or close to it. Settle on a convention and apply it everywhere:

  • Use lowercase snake_case: order_items, starts_at. Unquoted names are case-insensitive in most databases, and PostgreSQL folds them to lowercase, so mixed case causes confusion.
  • Pick plural or singular table names (customers or customer) and stick with it. This course uses plural.
  • Name foreign key columns after the table they point to: customer_id points at customers.id.
  • Use clear suffixes for common kinds of values: _at for timestamps, _on for dates, is_ or has_ for true/false flags.
  • Avoid reserved words like order, user, group or select. They only work when quoted ("order"), and everyone who writes a query against them later will trip over it. See SQL Syntax and Style.

Columns and data types

Every column gets a data type that tells the database what it holds. The core types work in every database, though the names vary:

Kind of data Standard SQL SQLite PostgreSQL MySQL SQL Server
Whole numbers INTEGER INTEGER integer, bigint INT, BIGINT int, bigint
Exact decimals (money) DECIMAL(10,2) NUMERIC (no exact decimal storage) numeric(10,2) DECIMAL(10,2) decimal(10,2)
Floating point REAL, DOUBLE PRECISION REAL double precision DOUBLE float
Text VARCHAR(n) TEXT text, varchar(n) VARCHAR(n), TEXT nvarchar(n)
Date and time DATE, TIMESTAMP TEXT in ISO format date, timestamptz DATE, DATETIME date, datetime2
True/false BOOLEAN INTEGER 0/1 boolean BOOLEAN (= TINYINT(1)) bit
Binary data BLOB BLOB bytea BLOB varbinary(max)

The Data Types lesson covers every choice in depth, including why money shouldn't be stored as REAL.

SQLite's flexible typing and STRICT tables

SQLite is unusual. In a normal SQLite table, a column's type is only a preference (its affinity): SQLite converts a value to that type when it can, and stores it as-is when it can't. Other databases reject the value instead.

SQL Flexible typing

Press Run (or Ctrl + Enter) to see the result here.

The text '19.0' looked like a number, so SQLite converted it to the real number 19.0. But 'broken' was stored as text in a REAL column. Most databases would have rejected that row. Since version 3.37, SQLite lets you opt into strict checking with the STRICT keyword after the closing parenthesis. Strict tables allow only the types INTEGER, REAL, TEXT, BLOB and ANY. The next example fails on purpose:

SQL A STRICT table

Press Run (or Ctrl + Enter) to see the result here.

'19.0' is still converted, because it can be done without losing information, but 'broken' now fails with Error: cannot store TEXT value in REAL column readings.temperature. Use STRICT for new SQLite tables when you want the safety other databases give you by default.

Primary keys

Almost every table should have a primary key: a column (or set of columns) whose value is unique and never NULL, so each row can be identified unambiguously. You can declare it on the column or, for several columns, as a table constraint:

SQL Single and composite primary keys

Press Run (or Ctrl + Enter) to see the result here.

Speaker 1 appears twice and event 1 appears twice, which is fine: only the combination must be unique. The last insert repeats the pair (1, 2) and fails on purpose with UNIQUE constraint failed: event_speakers.event_id, event_speakers.speaker_id.

In SQLite, a column declared exactly as INTEGER PRIMARY KEY is special: it becomes the table's internal row number (the rowid), so it's assigned automatically and lookups by it are as fast as possible. Other databases use identity columns for automatic numbers:

SQL
-- PostgreSQL (and the SQL standard)
CREATE TABLE speakers (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name text NOT NULL
);

-- MySQL
CREATE TABLE speakers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(200) NOT NULL
);

-- SQL Server
CREATE TABLE speakers (
  id int IDENTITY(1, 1) PRIMARY KEY,
  full_name nvarchar(200) NOT NULL
);

Auto-Increment and Identity Columns compares these in detail, and Keys explains how to choose a good primary key.

Defaults and a first look at constraints

Constraints are rules the database enforces on every insert and update, so bad data can't get in no matter which application writes it. The most common ones fit right into the column definitions:

SQL Defaults and constraints

Press Run (or Ctrl + Enter) to see the result here.

The first booking got 1 seat, status 'confirmed' and the current UTC time in booked_at, all from defaults. (The query shows the timestamp's length, 19 characters like 2026-03-01 14:05:09, because the actual value changes every run.) What each rule does:

  • NOT NULL: a value is required.
  • DEFAULT: the value used when an insert leaves the column out. It can be a constant or an expression like CURRENT_TIMESTAMP.
  • CHECK: any condition that must not be false for the row, here a range and a list of allowed values.
  • UNIQUE: no two rows may share the value; UNIQUE (event_id, email) stops anyone booking the same event twice.
  • REFERENCES (foreign keys), not used here, link a column to another table's key. They get their own lesson, Foreign Keys.

Try inserting 12 seats, or a status of 'maybe', and read the error. Constraints covers every rule, including generated columns.

IF NOT EXISTS and inspecting tables

Creating a table that already exists is an error: table events already exists. Setup scripts that may run more than once add IF NOT EXISTS, which skips the statement quietly when the table is there. It doesn't compare definitions: if the existing table has different columns, it stays as it is.

To see which tables exist and how they're defined, SQLite offers the sqlite_schema table (the table catalog, also known by its older name sqlite_master) and PRAGMA table_info:

SQL IF NOT EXISTS and inspecting

Press Run (or Ctrl + Enter) to see the result here.

The catalog lists the table and an automatic index named sqlite_autoindex_venues_1, which SQLite created to enforce UNIQUE on name. pragma_table_info() returns one row per column with its type, whether it's NOT NULL, its default and its position in the primary key. (The column is called notnull, a keyword, so it's quoted.)

Database Create only if missing List a table's columns
SQLite CREATE TABLE IF NOT EXISTS PRAGMA table_info(t)
PostgreSQL CREATE TABLE IF NOT EXISTS \d t in psql, or information_schema.columns
MySQL CREATE TABLE IF NOT EXISTS DESCRIBE t; or SHOW CREATE TABLE t;
SQL Server IF OBJECT_ID('t') IS NULL CREATE TABLE ... EXEC sp_help 't'; or INFORMATION_SCHEMA.COLUMNS

CREATE TABLE AS: tables from queries

CREATE TABLE ... AS SELECT creates a table and fills it with a query's result in one step. It's ideal for snapshots, report tables and quick experiments:

SQL CREATE TABLE AS ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The new table takes its column names from the query (that's why the aliases matter) and its rows from the result. But look at pragma_table_info: there's no primary key, nothing is NOT NULL, and the types are only approximations: plain columns get a type based on their source (INT, TEXT), while computed columns such as units_sold and revenue get no declared type at all. CREATE TABLE AS copies data, not rules. For a permanent table, write a proper CREATE TABLE with keys and constraints, then fill it with INSERT ... SELECT.

Adding WHERE false (or LIMIT 0) copies just the column structure with no rows. Other databases have their own variants:

SQL
-- PostgreSQL, MySQL, Oracle: same syntax
CREATE TABLE product_sales AS SELECT ...;

-- SQL Server: SELECT ... INTO
SELECT p.id AS product_id, p.name
INTO product_sales
FROM products AS p;

-- MySQL: copy a table's structure, including indexes, without data
CREATE TABLE products_backup LIKE products;

Temporary tables

A temporary table exists only for the current connection and disappears when it closes. It's private, so two sessions can each have their own temp_totals without clashing. Use one to hold intermediate results in a long script:

SQL
-- SQLite, PostgreSQL, MySQL
CREATE TEMPORARY TABLE big_spenders AS
SELECT customer_id, SUM(shipping) AS total_shipping
FROM orders
GROUP BY customer_id;

-- SQL Server: a # prefix makes a table temporary
CREATE TABLE #big_spenders (customer_id int, total_shipping decimal(10, 2));

SQLite also accepts the shorter CREATE TEMP TABLE. Often a CTE is a simpler choice, since it needs no cleanup at all.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement creates a table only when it doesn't exist yet?

    IF NOT EXISTS skips the statement when a table with that name exists. It works in SQLite, PostgreSQL and MySQL, but not in SQL Server.
  2. 2. A table has PRIMARY KEY (event_id, speaker_id). Which pair of rows is allowed?

    A composite key requires the combination to be unique. Repeating event_id is fine as long as the pair differs; primary key columns can't be NULL.
  3. 3. In a normal (non-STRICT) SQLite table, what happens when you insert 'broken' into a REAL column?

    SQLite's types are affinities. A value that can't be converted is stored as is. STRICT tables reject it instead.
  4. 4. What does CREATE TABLE copy AS SELECT * FROM products; NOT copy?

    CREATE TABLE AS copies names and data, but not keys, NOT NULL, CHECK, UNIQUE or defaults.
  5. 5. What does seats INTEGER NOT NULL DEFAULT 1 store when an INSERT leaves seats out?

    An omitted column takes its DEFAULT. NOT NULL would only cause an error if there were no default.

Summary

  • CREATE TABLE name (column type constraints, ..., table_constraints); defines a new table.
  • Use lowercase snake_case names, a consistent plural or singular style, and avoid reserved words.
  • Types differ between databases; SQLite treats them as preferences unless the table is STRICT.
  • Give every table a primary key; use a table constraint for composite keys. INTEGER PRIMARY KEY numbers rows automatically in SQLite.
  • NOT NULL, DEFAULT, CHECK and UNIQUE keep bad data out at the source.
  • IF NOT EXISTS makes setup scripts re-runnable; sqlite_schema and PRAGMA table_info show what exists.
  • CREATE TABLE AS SELECT copies data but not rules; temporary tables last for one connection.

Next: Primary, Candidate, Composite and Foreign Keys, where you'll learn how to choose the right keys for your tables.