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.
| Clause | Purpose | Example |
|---|---|---|
| Table name | Identifies the relation within a schema | sales.order_line |
| Column definition | Declares name, data type and column rules | quantity INTEGER NOT NULL |
| DEFAULT | Supplies a value when INSERT omits the column | DEFAULT 0 |
| Table constraint | Expresses rules spanning several columns | PRIMARY KEY (order_id, line_no) |
| IF NOT EXISTS | Skips creation when the table already exists | CREATE TABLE IF NOT EXISTS log |
| Storage options | Vendor-specific physical settings | ENGINE=InnoDB, TABLESPACE fast_ssd |
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.
| Constraint | What it guarantees | Typical side effect |
|---|---|---|
| NOT NULL | The column always has a value | Inserts without the value fail |
| PRIMARY KEY | Unique, non-null row identifier | Creates a unique index |
| UNIQUE | No duplicate values among non-null entries | Creates a unique index |
| FOREIGN KEY | Value exists in a referenced table | Adds checks on child and parent writes |
| CHECK | A Boolean expression holds for each row | Rejects rows that violate the rule |
| DEFAULT | A value is supplied when omitted | No validation by itself |
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.
| Variant | Behavior | Typical use |
|---|---|---|
| CREATE TABLE AS SELECT | Creates and fills a table from a query result | Snapshots, staging, reporting extracts |
| CREATE TABLE LIKE | Copies the column layout of another table | Clones for testing or archiving |
| CREATE TEMPORARY TABLE | Table visible to one session, dropped at session end | Intermediate results in long procedures |
| Partitioned table | Rows split by range, list or hash of a key | Very large time-series or tenant data |
| UNLOGGED or in-memory table | Skips or reduces write-ahead logging | Fast, disposable caches and queues |
| WITHOUT ROWID (SQLite) | Stores rows clustered by the primary key | Compact lookup tables |
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 compared with related DDL statements
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.
| Statement | Effect | Risk level |
|---|---|---|
| CREATE TABLE | Adds a new, empty structure | Low |
| ALTER TABLE | Changes columns or constraints of an existing table | Medium to high on large tables |
| DROP TABLE | Removes the table and all its rows | High and usually irreversible |
| TRUNCATE TABLE | Deletes all rows but keeps the structure | High for data, none for schema |
| CREATE VIEW | Stores a named query without its own data | Low |
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
DECIMALorNUMERICtypes 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.
| Mistake | Consequence | Better practice |
|---|---|---|
| FLOAT for currency | Rounding errors in sums | Use DECIMAL(12,2) or integer cents |
| Missing NOT NULL | Three-valued logic surprises in WHERE filters | Declare NOT NULL unless absence is meaningful |
| No foreign keys | Orphaned rows and silent inconsistencies | Declare references and index them |
| Oversized VARCHAR everywhere | Weak validation and larger memory grants | Size columns to realistic limits |
| Reserved words as names | Constant quoting and portability issues | Avoid names such as order or user |
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.