CREATE TABLE

On this page 8

CREATE TABLE is the SQL data definition statement that creates a new table in a relational database and fixes its structure: the column names, their data types, default values and the integrity constraints that every future row must satisfy. In SQL it belongs to DDL rather than DML, so it describes the shape of the data instead of changing the data itself.

The statement is defined by the ISO/IEC 9075 SQL standard, yet every engine extends it with its own options for storage, partitioning and physical layout. A carefully written CREATE TABLE is the first line of data quality, because rules declared in the schema are enforced by the database for every application that writes to it.

How CREATE TABLE defines a relation

A CREATE TABLE statement names the table and lists its column definitions in parentheses, optionally followed by table-level constraints. A minimal example is CREATE TABLE customer (id BIGINT PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP). Each column definition combines a name, a type and optional column constraints.

When the statement runs, the engine records the definition in the system catalog (for example information_schema or pg_catalog), allocates storage structures and usually builds an index for the primary key and every unique constraint. In most engines the table is empty afterward; rows arrive later through INSERT or a bulk load.

The parts of a typical statement and their roles are summarized below.

ClausePurposeExample
Table nameIdentifies the relation within a schemasales.order_line
Column definitionDeclares name, data type and column rulesquantity INTEGER NOT NULL
DEFAULTSupplies a value when INSERT omits the columnDEFAULT 0
Table constraintExpresses rules spanning several columnsPRIMARY KEY (order_id, line_no)
IF NOT EXISTSSkips creation when the table already existsCREATE TABLE IF NOT EXISTS log
Storage optionsVendor-specific physical settingsENGINE=InnoDB, TABLESPACE fast_ssd
Main clauses of a CREATE TABLE statement

Data types and column constraints

Choosing the narrowest correct data type is the most consequential decision in a table definition. The type determines storage size, sort order, valid operations and how much invalid input the engine rejects automatically. Storing dates as text or money as floating point creates errors that no later query can fully repair.

Constraints turn business rules into declarative guarantees. They are checked on every INSERT and UPDATE, regardless of which application or person issues the statement.

ConstraintWhat it guaranteesTypical side effect
NOT NULLThe column always has a valueInserts without the value fail
PRIMARY KEYUnique, non-null row identifierCreates a unique index
UNIQUENo duplicate values among non-null entriesCreates a unique index
FOREIGN KEYValue exists in a referenced tableAdds checks on child and parent writes
CHECKA Boolean expression holds for each rowRejects rows that violate the rule
DEFAULTA value is supplied when omittedNo validation by itself
Standard constraints and what they enforce

Foreign keys can declare referential actions such as ON DELETE CASCADE or ON DELETE RESTRICT, which decide what happens to child rows when a parent row disappears. Note that MySQL accepted CHECK syntax for years but ignored CHECK constraints before version 8.0.16, so behavior must be verified per engine.

Variants of the statement across engines

Beyond the standard form, the statement has several important variants that change how the table is populated, how long it lives or how it is stored. Knowing them avoids writing manual workarounds.

VariantBehaviorTypical use
CREATE TABLE AS SELECTCreates and fills a table from a query resultSnapshots, staging, reporting extracts
CREATE TABLE LIKECopies the column layout of another tableClones for testing or archiving
CREATE TEMPORARY TABLETable visible to one session, dropped at session endIntermediate results in long procedures
Partitioned tableRows split by range, list or hash of a keyVery large time-series or tenant data
UNLOGGED or in-memory tableSkips or reduces write-ahead loggingFast, disposable caches and queues
WITHOUT ROWID (SQLite)Stores rows clustered by the primary keyCompact lookup tables
Common CREATE TABLE variants

CREATE TABLE AS SELECT copies data and column types but generally does not copy constraints, indexes or defaults, which surprises many developers who expect a complete clone. Temporary tables also behave differently between engines: SQL Server prefixes them with #, while PostgreSQL and MySQL use the TEMPORARY keyword.

CREATE TABLE is one member of a family of schema-changing statements, and teams often confuse when each applies. The table below separates them by effect and risk.

StatementEffectRisk level
CREATE TABLEAdds a new, empty structureLow
ALTER TABLEChanges columns or constraints of an existing tableMedium to high on large tables
DROP TABLERemoves the table and all its rowsHigh and usually irreversible
TRUNCATE TABLEDeletes all rows but keeps the structureHigh for data, none for schema
CREATE VIEWStores a named query without its own dataLow
CREATE TABLE and neighboring DDL statements

Whether DDL participates in transactions also differs. PostgreSQL and SQL Server support transactional DDL, so a failed migration can roll back a CREATE TABLE cleanly, whereas MySQL and Oracle commit implicitly before and after most DDL statements.

Designing tables for production use

Good table design starts from the access patterns and invariants of the data rather than from the screens of a single application. Practitioners usually follow a few settled rules.

  • Give every table a stable primary key; choose between a surrogate key such as an identity column or UUID and a natural key based on how often the natural value changes.
  • Normalize to roughly third normal form first, then denormalize only for a measured performance need.
  • Use timezone-aware timestamps and exact DECIMAL or NUMERIC types for money.
  • Name constraints explicitly, for example CONSTRAINT fk_order_customer, so error messages and later migrations are readable.
  • Keep table definitions in version-controlled migration scripts instead of running ad hoc statements in production.

Physical choices matter as well. Column order affects row padding in some engines, and clustered storage, as used by InnoDB and SQL Server clustered indexes, makes the primary key choice decisive for insert speed and range scans.

Common mistakes and their consequences

Most problems with table definitions appear months later, when the table has grown and changing it requires a costly migration. The following errors recur most often.

MistakeConsequenceBetter practice
FLOAT for currencyRounding errors in sumsUse DECIMAL(12,2) or integer cents
Missing NOT NULLThree-valued logic surprises in WHERE filtersDeclare NOT NULL unless absence is meaningful
No foreign keysOrphaned rows and silent inconsistenciesDeclare references and index them
Oversized VARCHAR everywhereWeak validation and larger memory grantsSize columns to realistic limits
Reserved words as namesConstant quoting and portability issuesAvoid names such as order or user
Frequent design errors in table definitions

A further warning concerns implicit index creation: primary and unique constraints get indexes automatically, but foreign key columns usually do not, except in MySQL InnoDB. Unindexed foreign keys cause slow JOIN queries and lock contention during parent deletes.

The simple explanation

Think of a database table as a spreadsheet with strict rules. CREATE TABLE is the instruction that draws that spreadsheet before any data goes in. It decides what the columns are called, what kind of value each column accepts, such as a number, a date or a piece of text, and which rules must always hold.

For example, a rule can say that every customer must have an email address and that no two customers may share the same one. Once the table exists, the database checks these rules automatically every time someone adds or changes a row. The key idea is that good rules written at the start prevent bad data later.

How it is used in practice

An online store moves its order history from flat files into PostgreSQL. The development team writes a migration script containing CREATE TABLE orders with a BIGINT identity primary key, a customer_id foreign key referencing the customer table, a total NUMERIC(12,2) NOT NULL CHECK (total >= 0) column and a placed_at TIMESTAMPTZ NOT NULL DEFAULT now() column. Because order volume is high, the table is range-partitioned by month on placed_at.

During the first import of roughly 4 million historical rows, the CHECK and foreign key constraints reject about 3,000 records with negative totals or unknown customers. The team corrects them in a staging table before loading. The result is a clean order table whose rules now protect it against faulty writes from the web shop, the mobile app and the accounting export alike, and monthly partitions keep reporting queries fast.