SQL Data Definition and Schema Creation

Before a single row can be inserted or queried, a database needs a blueprint. That blueprint is called a schema. It is the collection of tables, columns, types, and rules that data must obey. It is built with a small but powerful sublanguage of SQL called the Data Definition Language (DDL). Every production database begins this way. This is true from a two-table hobby project to a bank's core ledger. All of them begin with `CREATE TABLE` statements. These statements decide, once and for all, what kinds of facts the system is willing to store. They also decide what mistakes it will refuse to accept. The motivating problem is simple to state. How do we translate an abstract Relational Model design into a concrete structure? That design consists of relations, attributes, and keys. The structure must be enforceable, one a database engine can check on every write. Without such enforcement, a common promise gets made: "the application will validate it." That promise is inevitably broken. Some forgotten script breaks it, or some ad-hoc migration, or some other application sharing the same database. Integrity constraints push correctness down into the data itself. That way, no matter which program touches the table, the guarantees hold. This matters in real systems for a clear reason. Schema design is where most of the load-bearing correctness of a database lives. A poorly chosen data type silently truncates values. A missing `NOT NULL` lets critical fields go empty. A missing foreign key lets an order reference a customer that no longer exists. Conversely, a well-designed schema changes things for the better. It turns entire classes of application bugs into database errors. Those errors are caught immediately, loudly, and consistently. This is far cheaper to fix than corrupted data discovered months later. This article walks through `CREATE TABLE` syntax and the standard SQL data types. It also covers the taxonomy of integrity constraints (`NOT NULL`, `UNIQUE`, `PRIMARY KEY`, `FOREIGN KEY`, `CHECK`, `DEFAULT`). It covers the DDL statements used to alter and destroy schema objects. It builds the vocabulary and mental model needed before writing `SELECT` queries. This foundation also supports defining views over these tables.

Key takeaways

DDL (`CREATE`, `ALTER`, `DROP`) defines schema; DML (`SELECT`, `INSERT`, `UPDATE`, `DELETE`) manipulates data within it.
`CREATE TABLE` names a relation, lists typed columns, and attaches integrity constraints in one declarative statement.
Primary keys enforce entity integrity (uniqueness + non-null); foreign keys enforce referential integrity across tables.
`CHECK` constraints encode business rules directly in the schema; `DEFAULT` supplies values when none are given.
The database engine validates every constraint on every `INSERT`/`UPDATE`/`DELETE`, rejecting violations atomically.
`ALTER TABLE` and `DROP TABLE` let schemas evolve, but constraint and cascade choices affect existing data non-trivially.

The core insight

A schema is a contract: `CREATE TABLE` declares not just what data looks like, but which data the system will refuse to store.

The idea

A relational schema is a named collection of relations (tables), each with a fixed set of typed attributes (columns), together with a set of integrity constraints that every valid database state must satisfy. SQL's Data Definition Language (DDL) is the subset of SQL used to create, modify, and destroy these schema objects: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, along with related statements for schemas, indexes, and views. DDL is distinct from the Data Manipulation Language (DML) — `SELECT`, `INSERT`, `UPDATE`, `DELETE` — which operates on the rows within a schema that DDL has already fixed. Each column in a `CREATE TABLE` statement is assigned a domain, expressed in SQL as a data type (`INTEGER`, `VARCHAR(n)`, `DECIMAL(p,s)`, `DATE`, `BOOLEAN`, and others). A data type constrains both the representation of values (how many bytes, what format) and the set of legal values a column may hold — an attempt to insert `'hello'` into an `INTEGER` column is rejected at the type level, before any business logic runs. Types alone, however, cannot express relationships like "every order must belong to an existing customer" or rules like "salary must be positive." Those require integrity constraints, which come in several flavors: domain constraints (`NOT NULL`, `CHECK`) restrict individual column values; key constraints (`PRIMARY KEY`, `UNIQUE`) enforce that certain attributes identify rows uniquely; and referential integrity constraints (`FOREIGN KEY`) enforce that values in one table correspond to existing values in another, tying the schema's tables together into a coherent whole rather than a set of unrelated files. The crucial design principle is that constraints are declared once, in the schema, and enforced by the database management system on every subsequent operation, regardless of which application or user issues it. This is what separates a relational database from a loose collection of spreadsheets: the guarantees are structural, not procedural, and they compose — a foreign key referencing a primary key that itself has a `CHECK` constraint gives you layered correctness for free.

How it works

Defining a schema is a sequence of DDL statements, typically issued in dependency order: tables with no foreign keys first, then tables that reference them. The engine processes each `CREATE TABLE` statement by (1) parsing the column list and types, (2) parsing and validating constraint clauses, (3) checking that referenced objects (tables, columns) already exist for foreign keys, and (4) recording the resulting schema in the system catalog — the metadata tables that the query processor consults for every later `SELECT`, `INSERT`, or plan-generation step.

General shape of `CREATE TABLE`: ```sql CREATE TABLE table_name ( column1 datatype [column_constraint ...], column2 datatype [column_constraint ...], ... [table_constraint_1], [table_constraint_2], ... ); ``` A concrete schema for a small order-management system, built incrementally in dependency order: ```sql CREATE TABLE customer ( cust_id INTEGER PRIMARY KEY, cust_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, credit_limit DECIMAL(10,2) DEFAULT 1000.00 CHECK (credit_limit >= 0) ); CREATE TABLE product ( prod_id INTEGER PRIMARY KEY, prod_name VARCHAR(50) NOT NULL, unit_price DECIMAL(8,2) NOT NULL CHECK (unit_price > 0) ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, cust_id INTEGER NOT NULL, order_date DATE NOT NULL DEFAULT CURRENT_DATE, FOREIGN KEY (cust_id) REFERENCES customer(cust_id) ON DELETE CASCADE ); CREATE TABLE order_item ( order_id INTEGER, prod_id INTEGER, quantity INTEGER NOT NULL CHECK (quantity > 0), PRIMARY KEY (order_id, prod_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE, FOREIGN KEY (prod_id) REFERENCES product(prod_id) ); ``` Several mechanisms are at work here, each worth naming precisely:

`PRIMARY KEY` marks one or more columns as the table's unique identifier. It implicitly enforces `NOT NULL` and `UNIQUE` together, and a table may have at most one. On `order_item`, the primary key is composite — `(order_id, prod_id)` — meaning uniqueness is checked on the pair, not each column separately.
`FOREIGN KEY ... REFERENCES` declares that a column's values must appear as a primary (or unique) key value in another table, or be `NULL` if the column is nullable. This is referential integrity.
`ON DELETE CASCADE` specifies referential action: deleting a customer automatically deletes their orders, rather than leaving `orders` rows pointing at nothing (or being blocked). Alternatives include `ON DELETE RESTRICT` (reject the delete), `SET NULL`, and `SET DEFAULT`.
`CHECK` attaches an arbitrary boolean predicate over the row's own columns — `quantity > 0`, `unit_price > 0` — enforced on insert and update.
`DEFAULT` supplies a value automatically when an `INSERT` omits the column, here using a literal and the system function `CURRENT_DATE`.

Schema evolution uses `ALTER TABLE`: ```sql ALTER TABLE customer ADD COLUMN phone VARCHAR(20); ALTER TABLE customer ADD CONSTRAINT chk_name CHECK (cust_name <> ''); ALTER TABLE product ALTER COLUMN unit_price TYPE DECIMAL(10,2); ALTER TABLE orders DROP CONSTRAINT fk_orders_cust; ``` and destruction uses `DROP TABLE`, which must respect dependency order (or use `CASCADE`) exactly as creation did, in reverse: ```sql DROP TABLE order_item; DROP TABLE orders; DROP TABLE product; DROP TABLE customer; ```

Pseudocode for how an engine processes `CREATE TABLE` (conceptually — real engines optimize and batch these steps within one system-catalog transaction): ``` function CREATE_TABLE(name, columns, constraints): if name exists in catalog: error "table already exists" for col in columns: validate_type(col.datatype) // known type, valid params (e.g. VARCHAR(n), n > 0) col.nullable = true // default unless overridden for c in constraints: switch c.kind: case NOT_NULL: mark column c.column as nullable = false case UNIQUE, PRIMARY_KEY: if PRIMARY_KEY and table already has a primary key: error "multiple primary keys" record uniqueness requirement over c.columns if PRIMARY_KEY: for col in c.columns: mark nullable = false case CHECK: validate that c.predicate references only this table's columns record predicate for enforcement on INSERT/UPDATE case FOREIGN_KEY: if c.ref_table not in catalog: error "referenced table does not exist" if c.ref_columns not a unique/primary key of ref_table: error "foreign key must reference a unique or primary key" record (c.columns -> c.ref_table, c.ref_columns, c.on_delete_action) persist new relation descriptor, column list, constraint list to system catalog allocate physical storage (heap file / pages) for the relation return success ``` ``` function INSERT_ROW(table, row): for col in table.columns: if row[col] is missing: row[col] = evaluate_default(col) // DEFAULT clause, else NULL if row[col] is NULL and col.nullable == false: reject "NOT NULL violation on " + col if row[col] is not NULL and not type_conforms(row[col], col.datatype): reject "type mismatch on " + col for uq in table.unique_constraints: if exists row2 in table where row2[uq.columns] == row[uq.columns]: reject "UNIQUE/PRIMARY KEY violation" for chk in table.check_constraints: if not evaluate(chk.predicate, row): reject "CHECK violation: " + chk.predicate for fk in table.foreign_keys: if row[fk.columns] is not NULL and not exists in fk.ref_table a row with fk.ref_columns == row[fk.columns]: reject "FOREIGN KEY violation" append row to table storage return success ```

Figure

Schema dependency graph: foreign keys point from child to parent table

Complexity & correctness

Time complexity of `CREATE TABLE` itself is small and largely fixed: parsing and validating a statement with $k$ columns and $c$ constraints takes $O(k + c)$ time, since each clause is checked independently against already-known catalog entries (existence of referenced tables, validity of type names). This is a one-time cost paid at schema-definition time, not per row, so it is essentially irrelevant to overall system performance — the interesting costs arise from enforcing constraints on every subsequent data modification.

Constraint enforcement cost per `INSERT`/`UPDATE` depends on which constraints exist and how they are implemented:

`NOT NULL` and type checks: $O(k)$ per row, where $k$ is the number of columns — a simple per-attribute scan.
`CHECK` constraints: $O(c)$ per row for $c$ predicates, each typically evaluated in constant time over the row's own attributes.
`UNIQUE` / `PRIMARY KEY`: naively $O(n)$ to scan all $n$ existing rows for a duplicate, but every real system builds an index (typically a B+-tree) on key columns, reducing the check to $O(\log n)$ expected/worst-case for lookup plus $O(\log n)$ for the subsequent index insertion — this is why primary keys are automatically indexed.
`FOREIGN KEY`: similarly $O(\log m)$ using an index on the referenced table's key, where $m$ is the size of the parent table, rather than $O(m)$ from a linear scan.

So the asymptotic per-row insertion cost, with proper indexing, is:

O\big(k + c + f \cdot \log m + \log n\big)

where $f$ is the number of foreign keys, $m$ is the largest referenced table's size, and $n$ is the table's own size (for the uniqueness/index insertion). Without indexes, uniqueness and referential checks would degrade to $O(n)$ and $O(m)$ respectively — a key reason database engines automatically index primary keys and strongly recommend indexing foreign key columns.

Space complexity is $O(k + c)$ for the schema metadata itself (column and constraint descriptors in the catalog) — negligible — plus $O(n)$ for the table's data and $O(n)$ per index built to support keys, which is the usual space/time tradeoff: indexes cost extra storage and slow down writes slightly in exchange for fast constraint checks and fast lookups later.

Correctness argument. The core claim is: if every `INSERT`/`UPDATE`/`DELETE` is checked against the declared constraints before being committed, then the database is always in a state satisfying all constraints — this is an invariant maintained by induction on the sequence of operations. Base case: an empty table trivially satisfies all constraints (vacuously, for `NOT NULL`, `UNIQUE`, `CHECK` over zero rows; foreign keys have no rows to violate them). Inductive step: assume the database satisfies all constraints before operation $i$ ; the engine's enforcement logic (as in the `INSERT_ROW` pseudocode above) explicitly re-checks every relevant constraint against the proposed new state and rejects the operation atomically if any constraint would be violated, so the state after operation $i$ either equals the state before (rejected) or is a state that has been explicitly verified to satisfy every constraint. By induction, every reachable state satisfies all constraints. This is precisely why constraints declared in DDL are stronger guarantees than validation code in an application: the invariant holds regardless of which client, script, or bug attempts to write, because enforcement lives in the engine, not the caller.

Honest tradeoffs. More constraints and more indexes mean slower writes and more storage; a table with five foreign keys and three unique constraints pays for five index lookups and three uniqueness checks on every insert. Distributed and NoSQL systems often relax these guarantees (eventual consistency, no foreign keys) precisely to avoid this write-time cost at scale, pushing integrity checking back into the application — trading enforced correctness for horizontal scalability. `CHECK` constraints, while powerful, cannot reference other tables in standard SQL (only triggers or application logic can enforce cross-table business rules beyond simple foreign keys), so they solve a narrower problem than they might appear to.

Worked example

Trace the schema above through a sequence of DDL and DML statements, showing exactly which constraint fires and why.

Step	Statement	Outcome	Reason
1	`INSERT INTO customer VALUES (1, 'Alice', 'alice@x.com', 500.00);`	Accepted	All `NOT NULL` satisfied; `credit_limit ≥ 0` holds; `email` unique so far.
2	`INSERT INTO customer VALUES (2, 'Bob', 'alice@x.com', 200.00);`	Rejected	`UNIQUE` on `email` — `'alice@x.com'` already used by cust_id 1.
3	`INSERT INTO customer VALUES (2, 'Bob', 'bob@x.com', -50.00);`	Rejected	`CHECK (credit_limit >= 0)` fails: `-50.00 < 0`.
4	`INSERT INTO customer VALUES (2, 'Bob', 'bob@x.com', 300.00);`	Accepted	All constraints now satisfied.
5	`INSERT INTO product VALUES (10, 'Widget', 4.50);`	Accepted	`unit_price > 0` holds.
6	`INSERT INTO orders VALUES (100, 1, DEFAULT);`	Accepted	`cust_id = 1` exists in `customer` (FK satisfied); `order_date` filled by `DEFAULT` → `CURRENT_DATE`.
7	`INSERT INTO orders VALUES (101, 99, '2024-01-01');`	Rejected	`FOREIGN KEY (cust_id)` — no row in `customer` with `cust_id = 99`.
8	`INSERT INTO order_item VALUES (100, 10, 3);`	Accepted	`order_id = 100` exists in `orders`, `prod_id = 10` exists in `product`; `quantity = 3 > 0`.
9	`INSERT INTO order_item VALUES (100, 10, -1);`	Rejected	Would duplicate primary key `(100, 10)` and violate `CHECK (quantity > 0)` — engine reports the first violation found.
10	`DELETE FROM customer WHERE cust_id = 1;`	Accepted, cascades	`ON DELETE CASCADE` on `orders.cust_id` — deletes customer 1, then automatically deletes order 100, which cascades again to delete `order_item (100, 10)`.

Step 10 is the subtlest: a single `DELETE` on `customer` ripples through two levels of foreign keys because `orders` was declared `ON DELETE CASCADE` from `customer`, and `order_item` was declared `ON DELETE CASCADE` from `orders`. Had `orders` instead used the default `ON DELETE RESTRICT` (or `NO ACTION`), step 10 would have been rejected outright, because deleting customer 1 while order 100 still references it would create a dangling reference — exactly the situation referential integrity exists to prevent. This single design choice (`CASCADE` vs. `RESTRICT`) changes system behavior dramatically and must be chosen deliberately per foreign key, not left to default by accident.

Common pitfalls & traps

Pitfall	Why it happens	Consequence
Forgetting `NOT NULL` on required columns	Columns are nullable by default in SQL	Silent `NULL`s propagate into aggregates and joins, producing wrong results downstream
Using `VARCHAR` without a sensible length limit, or too short a length	Guessing at data size, or copying a template	Truncated names/emails, or wasted validation as data grows
Declaring `FOREIGN KEY` without indexing the referencing column	Many engines auto-index the referenced (parent) key but not the child column	Every insert/delete does a full scan of the child table to check/cascade, becoming a major bottleneck at scale
Choosing `ON DELETE CASCADE` everywhere by default	Seems convenient, avoids "restrict" errors during development	A single top-level delete can silently wipe out large parts of the database in production
Using `CHECK` for cross-table business rules	`CHECK` predicates look powerful and general	Standard SQL forbids subqueries referencing other tables in `CHECK`; the constraint is rejected or silently not enforced portably
Adding a `PRIMARY KEY` after data already violates uniqueness	Schema evolves after data exists	`ALTER TABLE ... ADD PRIMARY KEY` fails until duplicates/`NULL`s are cleaned up first
Dropping a table referenced by foreign keys without `CASCADE` or prior cleanup	Forgetting dependency direction	`DROP TABLE` fails with a dependency error, or silently orphans data if forced

Pioneers & history

Origins · 1 minEdgar F. Codd and how this was discovered

Edgar F. Codd

1923–2003 · IBM San Jose Research Laboratory, birthplace of the relational model

Codd, a mathematician turned computer scientist, proposed the relational model in 1970 as a way to free applications from the physical storage details that plagued hierarchical and network databases of the era. His insight — that data should be described declaratively as relations satisfying constraints, independent of how it is stored or accessed — is the direct ancestor of every `CREATE TABLE` statement and every integrity constraint discussed here. Codd later formulated the twelve rules for relational database systems, several of which concern exactly the kind of constraint enforcement that DDL provides.

History

1970

— Codd publishes "A Relational Model of Data for Large Shared Data Banks," introducing relations, keys, and normalization.

1974

— Chamberlin and Boyce at IBM design SEQUEL (later SQL) as a readable query and definition language for Codd's model.

1979

— Oracle ships the first commercially available SQL-based relational database management system.

1986

— ANSI publishes the first standardized SQL specification (SQL-86), formalizing `CREATE TABLE` and constraint syntax.

1999

— SQL:1999 substantially extends constraint and trigger capabilities, cementing DDL's modern shape.

Where this matters

Every relational database system in production use — PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, SQLite, and cloud-native services like Amazon RDS and Google Cloud Spanner — implements `CREATE TABLE` and the constraint vocabulary described here as its foundational entry point. Object-relational mapping frameworks (Hibernate, Django ORM, ActiveRecord) generate DDL automatically from application-level model definitions, but the underlying guarantees they rely on are exactly primary keys, foreign keys, and `NOT NULL`/`CHECK` constraints. Database migration tools (Flyway, Liquibase, Alembic) exist specifically to version-control sequences of `ALTER TABLE` and `CREATE TABLE` statements as schemas evolve safely over an application's lifetime. Beyond application databases, data warehouses and analytics platforms (Snowflake, BigQuery, Redshift) use relaxed or optional constraint enforcement as a deliberate performance tradeoff, since bulk-loaded analytical data is typically validated upstream — illustrating that constraint enforcement is a design choice tuned to workload, not a universal requirement. Understanding schema definition is the prerequisite for everything that follows in relational databases: writing correct SELECT queries requires knowing exactly which columns can be `NULL` and which joins are backed by enforced foreign keys, and defining views over these tables means building new, derived relations on top of a foundation whose correctness is guaranteed by the constraints declared here.

Ask the tutor: How do `ALTER TABLE` constraint changes behave when the table already contains data that violates the new constraint?

Why can't application-level validation replace database constraints?

Application code only enforces rules for requests that pass through that specific application. Any other program, script, direct SQL client, or future service that writes to the same database bypasses that validation entirely. Database constraints, in contrast, are enforced by the engine itself on every write regardless of the caller, closing this gap structurally. They also compose safely: a foreign key plus a `CHECK` constraint on the referenced table gives layered guarantees no single application can replicate consistently. Constraints are also useful to the query optimizer, which can exploit known uniqueness or non-null guarantees to choose faster execution plans, a benefit pure application logic cannot provide.

What is the difference between a `PRIMARY KEY` and a `UNIQUE` constraint?

Both enforce that no two rows share the same value(s) in the specified column(s). The differences are that a table may have only one `PRIMARY KEY` but multiple `UNIQUE` constraints, and `PRIMARY KEY` columns are automatically `NOT NULL`, while `UNIQUE` columns may hold at most one `NULL` (SQL treats `NULL` as not equal to itself, so multiple `NULL`s are typically allowed under `UNIQUE`). Conventionally, the primary key is the column or columns chosen as the canonical identifier referenced by foreign keys elsewhere, while `UNIQUE` expresses a secondary uniqueness rule, like requiring distinct email addresses.

Can a `CHECK` constraint reference another table?

No, not in standard, portable SQL. A `CHECK` constraint's predicate must be evaluable using only the columns of the row being inserted or updated, since it is checked locally and independently for each row modification. Rules that depend on other tables — such as "total order quantity must not exceed inventory in the `product` table" — require a database trigger, an application-level check, or a `FOREIGN KEY` if the rule is one of simple referential existence. This is a real limitation of `CHECK`, and it is a common mistake to assume it can express arbitrary cross-table business logic.

Why does `VARCHAR(n)` matter if strings could just be unbounded?

Bounding string length lets the storage engine reason about row size, which affects how many rows fit per disk page and how efficiently the engine can pack, index, and cache data. It also acts as an early-warning integrity check: an email field capped at a reasonable length rejects obviously malformed input rather than silently accepting arbitrarily long garbage. Some systems distinguish `CHAR(n)` (fixed-width, padded) from `VARCHAR(n)` (variable-width, no padding) for further storage-layout control. Choosing too small a bound risks legitimate truncation; choosing "unbounded" everywhere sacrifices these storage and validation benefits.

What actually happens physically when `CREATE TABLE` runs?

The database engine parses the statement, validates types and constraints against the existing system catalog, and then writes new rows into internal catalog tables (sometimes literally called `pg_class`, `information_schema.tables`, or similar) describing the new relation's columns, types, and constraints. It typically also allocates initial physical storage — an empty heap file or set of pages — and creates any implicitly required indexes, such as one backing the primary key. None of the user's actual data rows exist yet; `CREATE TABLE` establishes the contract that subsequent `INSERT` statements must satisfy, and the query processor consults this same catalog metadata on every later `SELECT` for planning and type-checking.

What is the relationship between DDL and transactions?

Many modern relational systems, including PostgreSQL, support transactional DDL, meaning `CREATE TABLE`, `ALTER TABLE`, and similar statements can be wrapped in a transaction and rolled back if something later fails, just like `INSERT` or `UPDATE`. Others, notably MySQL and Oracle in older configurations, treat certain DDL statements as auto-committing, implicitly ending any open transaction the moment they run. This distinction matters enormously for migration scripts: assuming rollback safety on a system without transactional DDL can leave a schema in a half-migrated, inconsistent state if a later statement in the same script fails.

How does schema design here connect to normalization?

Choosing which tables to create, which columns belong together, and which foreign keys to declare is precisely the practical output of a normalization process performed on the Relational Model design. A well-normalized design — for example, separating `product` and `order_item` rather than repeating product details inside every order row — is what the `CREATE TABLE` statements in this article implement directly. Integrity constraints like foreign keys are the mechanism that keeps a normalized, multi-table design consistent; without them, splitting data across tables would only create opportunities for the pieces to drift out of sync.