SQL CREATE TABLE

The CREATE TABLE statement is how you tell a relational database exactly what shape your data will take before a single row exists. It defines the table’s name, its columns, the data type each column stores, and the rules (constraints) that protect the data’s integrity. Every other SQL operation — INSERT, SELECT, UPDATE, joins — depends on this schema being defined correctly, so understanding CREATE TABLE deeply is the foundation for everything else in SQL.

Overview: How CREATE TABLE Works

When you run a CREATE TABLE statement, the database engine does not store any data — it stores metadata. It records the table’s name in the system catalog (in SQLite this is the sqlite_master table; other engines use an information_schema), along with an ordered list of columns, each column’s declared data type, and any constraints attached to columns or to the table as a whole. From that point forward, every INSERT is checked against this metadata: does the number of values match the number of columns? Does each value satisfy its column’s type and constraints? Is a PRIMARY KEY or UNIQUE value actually unique? The engine also uses the schema to plan queries later — it knows which columns exist, which have indexes (a PRIMARY KEY automatically creates one), and which relationships (via FOREIGN KEY) connect tables together, all of which feeds into the query optimizer when you later write SELECT statements against the table.

Constraints are the mechanism that keeps your data trustworthy. A NOT NULL constraint stops a column from ever being empty. A UNIQUE constraint stops duplicate values. A CHECK constraint enforces a business rule like “salary must be positive.” A FOREIGN KEY constraint ties a column’s values back to a primary key in another table, preventing orphaned references. All of this is declared once, at creation time, so the database — not your application code — becomes the last line of defense against bad data.

Syntax

The general form of CREATE TABLE is:

CREATE TABLE table_name (
  column1 data_type constraints,
  column2 data_type constraints,
  ...
  table_level_constraints
);

Part	Meaning
table_name	The identifier used in every future query against this table.
column	A named field; every row in the table will have a value (or NULL) for it.
data_type	What kind of value is stored: INTEGER, TEXT, REAL, NUMERIC, BLOB in SQLite; similar concepts appear as INT, VARCHAR(n), DECIMAL, DATE in MySQL/PostgreSQL/SQL Server.
PRIMARY KEY	Uniquely identifies each row; implies NOT NULL and UNIQUE, and is automatically indexed.
NOT NULL	Rejects any INSERT/UPDATE that would leave the column empty.
UNIQUE	Rejects duplicate values in that column across all rows.
DEFAULT value	Supplies a value automatically when none is given in an INSERT.
CHECK (condition)	Rejects any row where the condition evaluates to false.
FOREIGN KEY (col) REFERENCES other_table(col)	Requires the value to exist in the referenced table’s key column.

SQLite’s INTEGER PRIMARY KEY has a special behavior worth knowing: it becomes an alias for the table’s internal row id, so it auto-increments by default even without an explicit AUTOINCREMENT keyword. Other engines use different syntax for auto-generated keys — MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY, and SQL Server uses IDENTITY(1,1).

Examples

Example 1: A basic table

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  enrollment_date TEXT
);

INSERT INTO students (student_id, first_name, last_name, enrollment_date) VALUES
(1, 'Ana', 'Silva', '2024-01-15'),
(2, 'Ben', 'Carter', '2024-02-01'),
(3, 'Chloe', 'Diaz', '2024-02-10');

SELECT * FROM students ORDER BY student_id;

Result:

student_id	first_name	last_name	enrollment_date
1	Ana	Silva	2024-01-15
2	Ben	Carter	2024-02-01
3	Chloe	Diaz	2024-02-10

Here student_id is the PRIMARY KEY, so it is automatically unique, non-null, and indexed. The two name columns are required (NOT NULL); enrollment_date is optional and stored as plain TEXT since SQLite has no dedicated date type — dates are commonly stored as ISO-8601 strings (YYYY-MM-DD) so they still sort correctly.

Example 2: Constraints in action

CREATE TABLE employees_demo (
  emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  department TEXT DEFAULT 'General',
  salary REAL CHECK (salary > 0)
);

INSERT INTO employees_demo (full_name, email, salary)
VALUES ('Grace Lee', 'grace.lee@example.com', 65000);

INSERT INTO employees_demo (full_name, email, department, salary)
VALUES ('Omar Faruk', 'omar.f@example.com', 'Engineering', 72000);

SELECT emp_id, full_name, department, salary FROM employees_demo ORDER BY emp_id;

Result:

emp_id	full_name	department	salary
1	Grace Lee	General	65000.0
2	Omar Faruk	Engineering	72000.0

Grace Lee’s row omitted department, so the DEFAULT 'General' kicked in automatically. Both rows satisfy salary > 0 and both emails are distinct, so the CHECK and UNIQUE constraints pass silently — you only notice them when a row tries to violate them.

Example 3: Linking tables with FOREIGN KEY

PRAGMA foreign_keys = ON;

CREATE TABLE departments (
  dept_id INTEGER PRIMARY KEY,
  dept_name TEXT NOT NULL
);

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  staff_name TEXT NOT NULL,
  dept_id INTEGER,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INSERT INTO departments (dept_id, dept_name) VALUES (1, 'Engineering'), (2, 'Marketing');
INSERT INTO staff (staff_id, staff_name, dept_id) VALUES (1, 'Nina Petrov', 1), (2, 'Sam Okafor', 2);

SELECT staff.staff_name, departments.dept_name
FROM staff
JOIN departments ON staff.dept_id = departments.dept_id
ORDER BY staff.staff_id;

Result:

staff_name	dept_name
Nina Petrov	Engineering
Sam Okafor	Marketing

The FOREIGN KEY on staff.dept_id guarantees every staff row points to a real row in departments. With PRAGMA foreign_keys = ON, trying to insert a staff row with dept_id = 99 (a department that doesn’t exist) would be rejected. This is also the pattern that later lets you write meaningful JOIN queries, because the relationship between the two tables was declared at creation time.

How It Works Step by Step

• Parsing: the engine reads the statement and validates the syntax — column names, data types, and constraint keywords must all be recognized.
• Catalog registration: the table’s definition (name, columns, types, constraints) is written into the database’s internal schema catalog, not into any data pages yet.
• Automatic indexing: a PRIMARY KEY (and any UNIQUE column) causes the engine to create a hidden index to enforce and speed up uniqueness lookups.
• Constraint attachment: NOT NULL, CHECK, and FOREIGN KEY rules are stored so every future INSERT/UPDATE is validated against them before the row is written.
• Ready for data: only after all of this succeeds can INSERT statements add rows, and only then can SELECT queries reference the table.

Common Mistakes

Mistake 1: Forgetting a comma between columns

CREATE TABLE bad_table (
  id INTEGER PRIMARY KEY
  name TEXT
);

This fails because SQL requires a comma between every column definition. Without it, the parser reads name TEXT as part of the id column’s definition and throws a syntax error. The fix is simply to add the missing comma:

CREATE TABLE good_table (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Mistake 2: Declaring more than one PRIMARY KEY

CREATE TABLE conflict_demo (
  id INTEGER PRIMARY KEY,
  code TEXT PRIMARY KEY
);

A table can only have one primary key, so attaching the PRIMARY KEY constraint to two separate columns raises an error. If you actually need both columns to jointly identify a row, declare a single composite primary key as a table-level constraint instead:

CREATE TABLE composite_demo (
  id INTEGER,
  code TEXT,
  PRIMARY KEY (id, code)
);

A related, quieter mistake is choosing a data type that’s too loose (e.g. storing dates or money as arbitrary TEXT/REAL without validation) and only discovering inconsistent formats after thousands of rows exist — constraints and consistent types are far cheaper to enforce at creation time than to clean up later.

Best Practices

• Always give every table an explicit primary key, even a simple auto-incrementing integer id, so every row can be uniquely and efficiently referenced.
• Use NOT NULL on any column your application logic assumes will always have a value — don’t rely on the application layer alone.
• Prefer CHECK constraints for simple business rules (positive amounts, valid enumerations) so invalid data is impossible to insert, not just unlikely.
• Declare FOREIGN KEY relationships explicitly, and turn on foreign key enforcement (PRAGMA foreign_keys = ON in SQLite; enabled by default in most other engines) so referential integrity is guaranteed, not assumed.
• Pick the narrowest, most accurate data type available for each column — it saves storage and helps the query planner reason about ranges and sorting correctly.
• Use CREATE TABLE IF NOT EXISTS in setup scripts that might run more than once, so re-running them doesn’t error out on tables that already exist.
• Name columns and tables consistently (e.g. singular vs. plural, snake_case) across your whole schema — inconsistency compounds into confusing joins later.

Practice Exercises

• Exercise 1: Create a table named books with columns book_id (primary key), title (required text), author (required text), and price (a number that must be greater than 0). Insert two rows and select them all back.
• Exercise 2: Create a table named categories with a primary key category_id and a unique category_name. Then create a table named items with its own primary key, an item name, and a category_id column that references categories(category_id). Insert one category and one item, then join them together in a SELECT.
• Exercise 3: Write a CREATE TABLE statement for a subscriptions table where the plan column defaults to 'free' when not specified. Insert one row without specifying plan and confirm the default was applied.

Summary

• CREATE TABLE defines a table’s columns, data types, and constraints before any data is inserted.
• PRIMARY KEY uniquely identifies rows and is automatically indexed and non-null.
• NOT NULL, UNIQUE, DEFAULT, and CHECK constrain individual columns to keep data valid.
• FOREIGN KEY links one table’s column to another table’s primary key, enforcing referential integrity.
• Every column definition must be separated by a comma, and a table can only have one primary key (use a composite key for multiple columns).
• Choosing the right constraints at creation time prevents bad data far more reliably than validating it after the fact.