CREATE TABLE - SQL | Varsity Tutors

SECTION 1

Historical Context & Motivation

Before relational databases existed, organizations stored data in flat files, hierarchical systems like IBM's IMS, and network-model databases governed by the CODASYL standard. These systems tightly coupled the physical storage layout to the application logic, meaning that any change to how data was stored on disk required rewriting the programs that accessed it. Edgar F. Codd, a mathematician at IBM's San Jose Research Laboratory, recognized that separating logical data organization from physical storage would yield tremendous benefits in flexibility and maintainability. His landmark 1970 paper, "A Relational Model of Data for Large Shared Data Banks," proposed organizing data into relations — mathematical tables with named columns and typed rows — and manipulating them through a declarative language rather than procedural navigation.

1970 • Codd's Relational Model

Edgar F. Codd publishes his seminal paper at IBM, proposing that data be organized into relations (tables) with a formal algebraic foundation, decoupling logical schema from physical storage.

1974 • SEQUEL at IBM

Donald Chamberlin and Raymond Boyce develop SEQUEL (Structured English Query Language) for IBM's System R prototype, introducing the declarative syntax for defining and querying tables that would become SQL.

1979 • Oracle's Commercial Release

Relational Software, Inc. (later Oracle Corporation) ships the first commercially available SQL-based RDBMS, making CREATE TABLE and other DDL statements accessible to enterprise developers.

1986 • ANSI SQL-86 Standard

The American National Standards Institute ratifies SQL as a formal standard (SQL-86), codifying the CREATE TABLE syntax along with data types, constraints, and access control across vendors.

1992–2023 • Ongoing SQL Evolution

Successive standards — SQL-92, SQL:1999, SQL:2003, SQL:2016, and SQL:2023 — add features such as CHECK constraints, generated columns, temporal tables, and JSON support, continually expanding what CREATE TABLE can express.

The central question that CREATE TABLE addresses is deceptively simple: how do you formally declare the structure, types, and integrity rules of a persistent data set so that a database engine can store, validate, and retrieve rows efficiently? Understanding its syntax and semantics is the entry point to every other SQL operation — you cannot insert, query, update, or delete data without first defining the table that holds it.

SECTION 2

Core Principles & Definitions

A CREATE TABLE statement belongs to the Data Definition Language (DDL) subset of SQL. Unlike DML statements (INSERT, SELECT, UPDATE, DELETE) that operate on rows, DDL statements operate on the metadata catalog — the internal repository that describes every object in the database. When you execute CREATE TABLE, the RDBMS writes an entry into the catalog (sometimes called the data dictionary or information schema) that records the table name, column names, data types, default values, and constraint definitions. Only after this catalog entry exists can the engine allocate physical storage and accept DML operations against the table.

Schema & Naming

Every table lives within a schema (a logical namespace). The fully qualified name follows the pattern schema_name.table_name. Naming conventions typically use snake_case, avoid SQL reserved words, and prefer plural nouns (e.g., students, enrollments).

Columns & Data Types

Each column declaration pairs an identifier with a data type — INTEGER, VARCHAR(n), DATE, BOOLEAN, etc. — which determines how the engine stores, indexes, and validates values.

Column Constraints

Constraints declared inline with a column — NOT NULL, UNIQUE, DEFAULT, CHECK — enforce domain integrity at the row level before data ever reaches application code.

Table Constraints

Constraints declared after all columns — PRIMARY KEY, FOREIGN KEY ... REFERENCES, composite UNIQUE — enforce entity and referential integrity across one or more columns.

Storage & Engine Options

Vendor extensions let you specify storage engines (e.g., InnoDB vs. MyISAM in MySQL), tablespaces, partitioning schemes, and compression — physical-layer decisions that do not alter the logical schema.

✦

KEY TAKEAWAY

Think of CREATE TABLE as drafting a blueprint before constructing a building. The blueprint specifies the dimensions, materials, and load-bearing rules (columns, data types, and constraints) so that every subsequent operation — pouring concrete, running wires, installing plumbing (inserting, updating, and querying data) — proceeds within a well-defined structural framework. Without the blueprint, workers would be placing materials arbitrarily, and the building could not guarantee structural safety. Similarly, without a properly constrained table definition, the database cannot guarantee data integrity.

SECTION 3

Visual Explanation — Anatomy of CREATE TABLE

The following diagram decomposes a complete CREATE TABLE statement into its syntactic constituents. Each colored region highlights a distinct structural element — the table identifier, column definitions with their data types, inline column constraints, and table-level constraints. Follow the color coding from top to bottom to see how the engine parses and catalogs each piece.

Each line of the CREATE TABLE statement is color-coded: purple for SQL keywords, blue for column identifiers, amber for data types, green for column-level constraints, and pink for table-level constraints. The parenthesized body between the opening and closing parentheses constitutes the column-definition list.

Observe that the statement begins with the CREATE TABLE keyword pair, followed by an optional schema-qualified name. Inside the parentheses, each line declares a column with its name, data type, and zero or more inline constraints. After all column definitions, table-level constraints such as PRIMARY KEY and FOREIGN KEY appear. This ordering is not arbitrary — many SQL parsers require column definitions before table constraints because the latter reference columns that must already be declared.

SECTION 4

How CREATE TABLE Works Internally

When the SQL engine receives a CREATE TABLE statement, it does not simply allocate disk space. The engine performs a multi-phase pipeline: parsing the SQL text into an abstract syntax tree, semantic analysis to validate identifiers and types, catalog mutation to record the schema metadata, and finally storage allocation to provision pages or extents on disk. Understanding this pipeline clarifies why errors like duplicate table names, unknown data types, or circular foreign-key references surface at specific phases.

The Generic Syntax Template

CREATE TABLE SYNTAX

CREATE TABLE [IF NOT EXISTS] schema.table_name ( col₁ type₁ [col_constraint₁ ...], col₂ type₂ [col_constraint₂ ...], ... [table_constraint₁], [table_constraint₂] );

Brackets [ ] denote optional clauses. IF NOT EXISTS prevents an error if the table already exists (supported in PostgreSQL, MySQL, SQLite; not in the SQL standard prior to SQL:2023). Each colᵢ is a column identifier, typeᵢ is a valid SQL data type, and constraints may appear inline (column-level) or after all columns (table-level).

Column Constraint Forms

COLUMN CONSTRAINT

column_name data_type [NOT NULL | NULL] [DEFAULT expr] [UNIQUE] [PRIMARY KEY] [CHECK (predicate)] [REFERENCES parent_table(col)]

Multiple constraints can be chained on a single column. NOT NULL disallows null values. DEFAULT expr supplies a value when none is provided on INSERT. REFERENCES enforces a foreign-key relationship inline.

Table Constraint Forms

TABLE CONSTRAINT

[CONSTRAINT name] PRIMARY KEY (col₁, col₂, ...) [CONSTRAINT name] FOREIGN KEY (colₐ) REFERENCES parent(colᵦ) [ON DELETE action] [ON UPDATE action] [CONSTRAINT name] UNIQUE (col₁, col₂, ...) [CONSTRAINT name] CHECK (predicate)

The optional CONSTRAINT name clause assigns a user-readable identifier, which is invaluable for debugging constraint violations. ON DELETE and ON UPDATE specify referential actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION.

💡

Implicit Index Creation

Most RDBMSs automatically create a unique B-tree index when you declare a PRIMARY KEY or UNIQUE constraint. In PostgreSQL, the primary key also becomes the clustering index for the heap table. This means that constraint declarations are not just about logical correctness — they have direct performance implications for query execution plans.

SECTION 5

Data Type Classification & Constraints Deep Dive

Choosing the correct data type is one of the most consequential decisions in schema design. An over-generous type — storing a boolean flag in a VARCHAR(255) — wastes storage and defeats index efficiency. A too-restrictive type — using SMALLINT for a counter that could exceed 32,767 — leads to overflow errors in production. The table below summarizes the most commonly used SQL data types grouped by category.

Category	Type	Description	Typical Size
Numeric	`INTEGER / INT`	Signed 32-bit integer (−2³¹ to 2³¹ − 1)	4 bytes
Numeric	`BIGINT`	Signed 64-bit integer	8 bytes
Numeric	`DECIMAL(p, s)`	Exact numeric with p total digits, s after decimal	Variable
Numeric	`REAL / FLOAT`	IEEE 754 floating point (approximate)	4–8 bytes
Character	`CHAR(n)`	Fixed-length string, padded with spaces	n bytes
Character	`VARCHAR(n)`	Variable-length string, up to n characters	≤ n + overhead
Character	`TEXT`	Unbounded variable-length string (vendor-specific)	Variable
Temporal	`DATE`	Calendar date (year, month, day)	4 bytes
Temporal	`TIMESTAMP`	Date + time (with or without time zone)	8 bytes
Boolean	`BOOLEAN`	TRUE, FALSE, or NULL (three-valued logic)	1 byte

Common SQL data types with storage characteristics

When a row is inserted or updated, the RDBMS evaluates constraints in a logical sequence. Failures at any stage produce SQLSTATE error codes and abort the transaction (unless handled by application-level exception blocks). The exact order may vary across implementations, but conceptually NOT NULL and type checks precede uniqueness and referential checks.

The constraint enforcement flow underscores a critical principle: constraints are declarative guarantees. Rather than writing procedural validation logic in every application that touches the database, you declare the rules once in the schema and the engine enforces them uniformly for every client. This is a direct application of the database community's principle that integrity logic belongs in the schema, not the application.

SECTION 6

Worked Example — Designing a Course Enrollment Schema

Suppose a university needs a database to track departments, courses, and student enrollments. We will design three interrelated tables, focusing on choosing appropriate data types, enforcing referential integrity, and using constraints to model real-world business rules.

Building a Three-Table Enrollment Schema

1
Step 1 — Identify Entities and Relationships

We have three entities: departments (each with a unique code and name), courses (belonging to exactly one department, with a credit count), and enrollments (a many-to-many junction between students and courses with a grade). Each course references a department; each enrollment references a student and a course.
2
Step 2 — Create the Departments Table

We define departments first because other tables reference it. We use CHAR(4) for the department code (e.g., 'CSCI', 'MATH') since codes are fixed-width, and VARCHAR(120) for the name. CREATE TABLE departments ( dept_code CHAR(4) NOT NULL, dept_name VARCHAR(120) NOT NULL, building VARCHAR(80), PRIMARY KEY (dept_code) );

Table departments created with dept_code as the primary key.
3
Step 3 — Create the Courses Table with a Foreign Key

The courses table uses a surrogate INTEGER primary key generated by a sequence (or auto-increment). The credits column has a CHECK constraint limiting values to 1–5. We reference departments(dept_code) via a foreign key with ON DELETE RESTRICT to prevent deleting a department that still has courses. CREATE TABLE courses ( course_id INTEGER GENERATED ALWAYS AS IDENTITY, course_code VARCHAR(10) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL, credits SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 5), dept_code CHAR(4) NOT NULL, PRIMARY KEY (course_id), FOREIGN KEY (dept_code) REFERENCES departments(dept_code) ON DELETE RESTRICT ON UPDATE CASCADE );

Table courses created with an identity PK, a unique natural key on course_code, a CHECK on credits, and a FK to departments.
4
Step 4 — Create the Enrollments Junction Table

The enrollments table models the many-to-many relationship between students and courses. It uses a composite primary key of (student_id, course_id) to prevent duplicate enrollments. The grade column is nullable because a student may not yet have a grade. ON DELETE CASCADE on the student FK means that if a student record is removed, their enrollments are automatically purged. CREATE TABLE enrollments ( student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE, grade CHAR(2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE RESTRICT );

Table enrollments created with a composite PK, two foreign keys with different referential actions, and a DEFAULT on enrolled_on.
5
Step 5 — Verify the Schema

After executing all three statements, verify the schema by querying the information schema catalog: SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'public' ORDER BY table_name, ordinal_position; This query returns every column definition you just created, confirming that the catalog accurately reflects your DDL.

Three interrelated tables verified in the information_schema — schema design complete.

SECTION 7

Vendor Variations & Trade-Offs

While the SQL standard specifies the core CREATE TABLE syntax, every major RDBMS adds proprietary extensions and sometimes diverges from the standard in subtle ways. Understanding these differences is essential if you need to write portable SQL or migrate schemas between platforms. The table below compares key behaviors across four widely used systems.

Feature	PostgreSQL	MySQL (InnoDB)	SQLite
Auto-increment PK	`GENERATED ALWAYS AS IDENTITY`	`AUTO_INCREMENT`	`INTEGER PRIMARY KEY` (implicit rowid alias)
`IF NOT EXISTS`	Supported	Supported	Supported
`CHECK` constraints	Fully enforced	Enforced since MySQL 8.0.16	Fully enforced
FK enforcement	Always on	InnoDB only; MyISAM ignores	Off by default; enable with `PRAGMA foreign_keys = ON`
Schema qualification	`schema.table`	`database.table` (schemas ≡ databases)	Single database per file; `ATTACH` for multiple
Transactional DDL	Yes — `CREATE TABLE` is rollback-safe	No — implicit commit before and after DDL	Yes

Key vendor differences for CREATE TABLE behavior

✦

KEY TAKEAWAY

Vendor-specific extensions are like regional building codes applied on top of international structural engineering standards. The fundamental physics of load-bearing walls (column types and constraints) is universal, but the permitting process, approved materials list, and inspection order differ by jurisdiction. When writing production schemas, always test your DDL against your target engine's specific behavior — particularly for foreign-key enforcement, transactional DDL, and auto-increment semantics.

SECTION 8

Connection to Advanced Schema Design

The CREATE TABLE statement you have studied is the first-normal-form entry point into a much richer landscape of schema design techniques. As systems scale and requirements become more complex, you will encounter advanced DDL features that extend the basic table definition in powerful ways.

Basic CREATE TABLE	Advanced Extension	Use Case
Single flat table	`CREATE TABLE ... PARTITION BY`	Horizontal partitioning for tables with billions of rows (e.g., time-series data partitioned by month)
Static columns	Generated / computed columns	Derive values automatically (e.g., `full_name VARCHAR GENERATED ALWAYS AS (first_name \|\| ' ' \|\| last_name)`)
Current-state only	Temporal / system-versioned tables	Automatic history tracking with `PERIOD FOR SYSTEM_TIME` (SQL:2011)
Relational columns	JSON / JSONB columns	Semi-structured data storage with path-based indexing (SQL:2016)
Independent tables	`CREATE TABLE ... INHERITS`	Table inheritance in PostgreSQL for polymorphic schemas (non-standard)

From basic CREATE TABLE to advanced DDL extensions

Additionally, the discipline of database normalization (1NF through BCNF, 4NF, and 5NF) provides a formal methodology for deciding which columns belong in which table. Normalization theory directly informs how you decompose a single overloaded CREATE TABLE into a set of well-structured, anomaly-free relations connected by foreign keys. Similarly, the field of physical database design concerns itself with index selection, storage parameters, and partitioning strategies that are specified at table creation time but affect query performance for the lifetime of the schema. Mastering CREATE TABLE is therefore not an endpoint but a gateway to these deeper topics.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain the difference between a column-level constraint and a table-level constraint. Under what circumstances must you use a table-level constraint instead of a column-level one?

Show answer

A column-level constraint is declared inline with a single column definition and can only reference that column. A table-level constraint is declared after all column definitions and may reference multiple columns. You must use a table-level constraint when the rule involves more than one column — for example, a composite primary key like PRIMARY KEY (student_id, course_id) or a CHECK constraint that compares two columns, such as CHECK (end_date > start_date). Semantically, both forms create the same enforcement mechanism; the distinction is purely syntactic and relates to whether the constraint's scope is one column or many.

Question 2: BASIC

Write a CREATE TABLE statement for a table named products with the following columns: product_id (integer, primary key), name (variable-length string up to 150 characters, not null), price (decimal with 8 digits total and 2 decimal places, must be positive), and created_at (timestamp defaulting to the current timestamp).

Show answer

CREATE TABLE products (
    product_id   INTEGER        NOT NULL,
    name         VARCHAR(150)   NOT NULL,
    price        DECIMAL(8, 2)  NOT NULL CHECK (price > 0),
    created_at   TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id)
);

The CHECK constraint price > 0 ensures only positive values are accepted. The DEFAULT clause on created_at means an INSERT that omits this column will automatically record the transaction's current timestamp.

Question 3: INTERMEDIATE

Design two tables — authors and books — where each book is written by exactly one author. The authors table should have an auto-generated integer primary key and a unique email column. The books table should reference the author and enforce that the publication year is between 1450 and the current year (approximate with 2025). If an author is deleted, their books should be automatically removed.

Show answer

CREATE TABLE authors (
    author_id  INTEGER       GENERATED ALWAYS AS IDENTITY,
    name       VARCHAR(200)  NOT NULL,
    email      VARCHAR(255)  NOT NULL UNIQUE,
    PRIMARY KEY (author_id)
);

CREATE TABLE books (
    book_id     INTEGER       GENERATED ALWAYS AS IDENTITY,
    title       VARCHAR(300)  NOT NULL,
    isbn        CHAR(13)      UNIQUE,
    pub_year    SMALLINT      NOT NULL CHECK (pub_year BETWEEN 1450 AND 2025),
    author_id   INTEGER       NOT NULL,
    PRIMARY KEY (book_id),
    FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
        ON DELETE CASCADE
);

The ON DELETE CASCADE on the foreign key ensures that removing an author automatically deletes all their books, fulfilling the requirement. The CHECK constraint on pub_year prevents unreasonable values, and GENERATED ALWAYS AS IDENTITY produces unique surrogate keys without manual management.

Question 4: APPLIED

A hospital system requires a table appointments that records patient visits to doctors. Business rules: (1) A patient cannot book two appointments with the same doctor on the same date. (2) The appointment start time must be before the end time. (3) If a doctor leaves the system, their future appointments should be set to reference NULL rather than being deleted. Write the CREATE TABLE statement encoding all three rules as constraints.

Show answer

CREATE TABLE appointments (
    appointment_id  INTEGER      GENERATED ALWAYS AS IDENTITY,
    patient_id      INTEGER      NOT NULL,
    doctor_id       INTEGER,
    appt_date       DATE         NOT NULL,
    start_time      TIME         NOT NULL,
    end_time        TIME         NOT NULL,
    reason          VARCHAR(500),
    PRIMARY KEY (appointment_id),
    UNIQUE (patient_id, doctor_id, appt_date),
    CHECK (end_time > start_time),
    FOREIGN KEY (patient_id)
        REFERENCES patients(patient_id)
        ON DELETE CASCADE,
    FOREIGN KEY (doctor_id)
        REFERENCES doctors(doctor_id)
        ON DELETE SET NULL
);

Rule (1) is enforced by the composite UNIQUE constraint on (patient_id, doctor_id, appt_date). Rule (2) is enforced by the CHECK constraint comparing end_time and start_time. Rule (3) is enforced by ON DELETE SET NULL on the doctor FK — note that doctor_id must be nullable (no NOT NULL) for SET NULL to work.

Question 5: CRITICAL THINKING

Consider a multi-tenant SaaS application where each tenant's data is stored in the same physical table. A developer proposes the following schema:

CREATE TABLE invoices (
    invoice_id  INTEGER PRIMARY KEY,
    tenant_id   INTEGER NOT NULL,
    amount      DECIMAL(10,2) NOT NULL,
    UNIQUE (invoice_id)
);

Identify at least three design problems with this schema. Propose a corrected version and justify each change, considering data isolation, key design, and constraint effectiveness.

Show answer

Problem 1: The UNIQUE (invoice_id) constraint is redundant since invoice_id is already the PRIMARY KEY — it wastes an extra index. Problem 2: There is no foreign key on tenant_id, so the database cannot guarantee that every invoice belongs to a valid tenant — referential integrity is missing. Problem 3: In a multi-tenant context, uniqueness of invoice numbers should typically be scoped per tenant, not globally. An invoice number '1001' might exist for two different tenants. A better design adds a natural invoice_number column with a composite unique constraint, a FK to a tenants table, and a CHECK on amount. Corrected:

CREATE TABLE invoices (invoice_id INTEGER GENERATED ALWAYS AS IDENTITY, tenant_id INTEGER NOT NULL, invoice_number VARCHAR(50) NOT NULL, amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0), PRIMARY KEY (invoice_id), UNIQUE (tenant_id, invoice_number), FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id));

This version separates the surrogate key from the business key, enforces referential integrity, scopes uniqueness per tenant, and validates amounts.

SUMMARY

Summary

The CREATE TABLE statement is the cornerstone of SQL's Data Definition Language, enabling you to declare a table's column names, data types (INTEGER, VARCHAR, DECIMAL, DATE, BOOLEAN, and more), and integrity constraints in a single declarative statement. Column-level constraints (NOT NULL, DEFAULT, CHECK, UNIQUE) enforce domain rules on individual columns, while table-level constraints (PRIMARY KEY, FOREIGN KEY, composite UNIQUE) enforce entity and referential integrity across multiple columns.

Understanding the vendor-specific variations (auto-increment syntax, CHECK enforcement history, transactional DDL) is essential for writing portable schemas. The constraint enforcement pipeline — from NOT NULL and type checking through CHECK predicates to uniqueness and referential lookups — illustrates how the RDBMS acts as a declarative guardian of data quality. Mastering CREATE TABLE prepares you for advanced topics including normalization, partitioning, generated columns, and temporal tables — all of which build upon the foundational schema definition that CREATE TABLE provides.