SQL 8 🛢️ Creating Tables with CREATE TABLE

The CREATE TABLE statement is the first DDL statement most developers write. It defines a new table in the database: the table name, the columns, their data types, and the constraints that govern what data can be stored. Every table in every relational database was created by a statement like this.

The LFCA and SQL chapters in this series have covered the relational model, the RDBMS, and the data types. This chapter puts those concepts together. The CREATE TABLE statement is where the abstract design becomes concrete. The table name must be unique within its schema. The columns must have valid data types. The constraints must be consistent with the relationships the table participates in.

Key point: CREATE TABLE is a DDL statement. It defines the structure, not the data. The statement is auto-committed in most RDBMSs and cannot be rolled back within a transaction. Once the table is created, its structure is fixed until an ALTER TABLE statement changes it. The CREATE TABLE statement is the contract that the database enforces for every subsequent INSERT, UPDATE, and DELETE.

---

Why CREATE TABLE matters

A table is not just a container for data. It is a set of rules. The rules determine what data can be stored, how it is validated, and how it relates to other tables. The CREATE TABLE statement is where those rules are declared.

The structure problem. A table without a defined structure is a spreadsheet. Any value can go in any column. A table with a defined structure rejects values that do not match the column’s type. The database enforces the structure on every insert and update.

The constraint problem. A table can have constraints beyond the data types. The NOT NULL constraint requires a value. The UNIQUE constraint prevents duplicates. The CHECK constraint validates a condition. The PRIMARY KEY constraint uniquely identifies each row. The FOREIGN KEY constraint connects the table to another table. Each constraint is a rule that the database enforces .

The relationship problem. A table rarely stands alone. It is connected to other tables through foreign keys. The CREATE TABLE statement declares the foreign keys, and the database enforces referential integrity. An order cannot reference a customer that does not exist. A loan cannot reference a book that is not in the catalog .

The default problem. A column can have a default value. When an INSERT statement does not provide a value for the column, the default is used. The DEFAULT clause is declared in the CREATE TABLE statement. A created_at column with DEFAULT CURRENT_TIMESTAMP automatically records the time of insertion .

The trade-off. CREATE TABLE is rigid. The structure must be defined before any data is inserted. Changing the structure later requires an ALTER TABLE statement, which may lock the table and take time on a large table. The rigidity is the price of consistency. A table without structure is flexible but unreliable.

---

a. The Basic Syntax

The CREATE TABLE statement has a simple structure. The table name is followed by a parenthesized list of column definitions and table constraints, separated by commas.

CREATE TABLE table_name (
    column1 data_type [column_constraints],
    column2 data_type [column_constraints],
    ...
    [table_constraints]
);

The column definition specifies the column name, the data type, and any column-level constraints. The table constraints are declared after the columns. The distinction between column constraints and table constraints matters for composite keys and for constraints that reference multiple columns.

A minimal CREATE TABLE statement:

CREATE TABLE movies (
    title CHAR(20),
    director CHAR(10),
    actor CHAR(10)
);

This creates a table with three columns. No constraints are declared. The columns accept any value of the specified type, including NULL .

A more complete example with constraints:

CREATE TABLE employees (
    employee_id INT NOT NULL PRIMARY KEY,
    first_name CHAR(20),
    last_name CHAR(20),
    department CHAR(10),
    salary INT DEFAULT 0
);

The employee_id column is the primary key and cannot be null. The salary column has a default value of 0. The other columns are nullable .

---

b. Column Constraints

Column constraints are declared after the column’s data type. They apply to the column they are attached to.

NOT NULL specifies that the column cannot contain a null value. Every row must have a value for the column .

first_name VARCHAR(50) NOT NULL

PRIMARY KEY specifies that the column is the primary key. The primary key uniquely identifies each row. It cannot be null and cannot be duplicated. A table can have only one primary key .

employee_id INT PRIMARY KEY

UNIQUE specifies that the values in the column must be unique across all rows. Unlike the primary key, a table can have multiple UNIQUE columns. A UNIQUE column can contain null values (in most databases), but the nulls are considered distinct from each other .

email VARCHAR(100) UNIQUE

CHECK specifies a condition that the value must satisfy. The condition is evaluated on every insert and update. If the condition is false, the operation is rejected .

age SMALLINT CHECK (age > 0)

DEFAULT specifies a value that is used when the INSERT statement does not provide a value for the column .

salary INT DEFAULT 0

REFERENCES is a shorthand for declaring a foreign key. It specifies that the column references the primary key of another table .

department_id INT REFERENCES departments(department_id)

---

c. Table Constraints

Table constraints are declared after all the column definitions. They are used for composite keys and for constraints that reference multiple columns.

Composite primary key. A primary key can consist of more than one column. The constraint is declared at the table level.

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

The combination of order_id and product_id is unique. Neither column is unique on its own .

Named foreign key. A foreign key can be declared with a name, which is useful for documentation and for referencing the constraint in error messages.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The CONSTRAINT fk_customer clause names the foreign key. The ON DELETE and ON UPDATE actions determine what happens when the referenced row is deleted or updated. CASCADE propagates the operation. RESTRICT rejects it. SET NULL sets the foreign key to null .

CONSTRAINT fk_loan_member
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
    ON DELETE CASCADE ON UPDATE CASCADE

Named CHECK constraint. A check constraint can be declared at the table level and can reference multiple columns.

CONSTRAINT valid_dates
    CHECK (start_date <= end_date)

---

Complete Example Session

This session builds a small library schema with three tables, demonstrating the CREATE TABLE statement in practice.

-- ============================================
-- PART 1: THE BOOKS TABLE
-- ============================================

CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Genre VARCHAR(50) NOT NULL,
    Quantity INT NOT NULL DEFAULT 0
);

-- ISBN is the primary key.
-- Title, Author, and Genre are required.
-- Quantity has a default of 0.

-- ============================================
-- PART 2: THE MEMBERS TABLE
-- ============================================

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(150) NOT NULL UNIQUE,
    Phone VARCHAR(20)
);

-- MemberID is the primary key.
-- Email is unique and required.
-- Phone is nullable.

-- ============================================
-- PART 3: THE LOANS TABLE WITH FOREIGN KEYS
-- ============================================

CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    CONSTRAINT fk_loan_member
        FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_loan_book
        FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

-- LoanID is the primary key.
-- MemberID references Members.
-- ISBN references Books.
-- Deleting a member cascades to their loans.
-- Deleting a book with loans is restricted.

-- ============================================
-- PART 4: THE IF NOT EXISTS CLAUSE
-- ============================================

CREATE TABLE IF NOT EXISTS Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE
);

-- The IF NOT EXISTS clause prevents an error if the table already exists.
-- The table is not created if it already exists.

-- ============================================
-- PART 5: THE COMPOSITE PRIMARY KEY
-- ============================================

CREATE TABLE Authorships (
    AuthorID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    PRIMARY KEY (AuthorID, ISBN),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

-- The primary key is the combination of AuthorID and ISBN.
-- The same author cannot be listed twice for the same book.

-- ============================================
-- PART 6: THE CHECK CONSTRAINT
-- ============================================

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10, 2) CHECK (Salary >= 0),
    TerminationDate DATE,
    CONSTRAINT valid_dates CHECK (TerminationDate IS NULL OR TerminationDate >= HireDate)
);

-- Salary must be non-negative.
-- TerminationDate must be null or after HireDate.

-- ============================================
-- PART 7: THE AUTO-INCREMENTING KEY
-- ============================================

-- PostgreSQL
CREATE TABLE Products (
    ProductID SERIAL PRIMARY KEY,
    ProductName VARCHAR(200) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0)
);

-- MySQL
CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(200) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0)
);

-- SQL Server
CREATE TABLE Products (
    ProductID INT IDENTITY(1, 1) PRIMARY KEY,
    ProductName VARCHAR(200) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0)
);

-- SQLite
CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
    ProductName VARCHAR(200) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0)
);

-- The auto-increment syntax varies by database.

-- ============================================
-- PART 8: THE DEFAULT VALUES
-- ============================================

CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY,
    CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
    OrderDate DATE NOT NULL DEFAULT CURRENT_DATE,
    Status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (Status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    CreatedAt TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- OrderDate defaults to the current date.
-- Status defaults to 'pending' and is restricted to a set of values.
-- CreatedAt defaults to the current timestamp.

-- ============================================
-- PART 9: THE TEMPORARY TABLE
-- ============================================

CREATE TEMPORARY TABLE TempResults (
    ID INT,
    Value DECIMAL(10, 2)
) ON COMMIT DROP;

-- The temporary table is visible only in the current session.
-- ON COMMIT DROP removes the table at the end of the transaction.

-- ============================================
-- PART 10: THE SCHEMA SUMMARY
-- ============================================

-- Books: ISBN (PK), Title, Author, Genre, Quantity
-- Members: MemberID (PK), Name, Email (UNIQUE), Phone
-- Loans: LoanID (PK), MemberID (FK), ISBN (FK), LoanDate, ReturnDate
-- Authorships: AuthorID (FK), ISBN (FK), PK (AuthorID, ISBN)
-- Employees: EmployeeID (PK), Salary (CHECK), valid_dates (CHECK)
-- Orders: OrderID (PK), CustomerID (FK), Status (DEFAULT, CHECK)

The ten parts cover the Books table, the Members table, the Loans table with foreign keys, the IF NOT EXISTS clause, the composite primary key, the check constraint, the auto-incrementing key, the default values, the temporary table, and the schema summary.

---

Quick Reference

The CREATE TABLE Syntax

Element	Purpose
CREATE TABLE	Creates a new table
IF NOT EXISTS	Prevents error if table exists
Column definition	Name, type, constraints
Table constraint	Applies to multiple columns
TEMPORARY	Creates a session-scoped table

The Column Constraints

Constraint	Purpose
NOT NULL	Requires a value
PRIMARY KEY	Unique identifier
UNIQUE	No duplicate values
CHECK	Value must satisfy condition
DEFAULT	Value when none provided
REFERENCES	Foreign key shorthand

The Table Constraints

Constraint	Purpose
PRIMARY KEY (col1, col2)	Composite key
FOREIGN KEY (col) REFERENCES tbl(col)	Named foreign key
CHECK (condition)	Multi-column check
UNIQUE (col1, col2)	Multi-column unique

The Referential Actions

Action	Behavior
ON DELETE CASCADE	Delete referencing rows
ON DELETE RESTRICT	Reject the delete
ON DELETE SET NULL	Set foreign key to null
ON UPDATE CASCADE	Propagate the update

The Auto-Increment Syntax

Database	Syntax
PostgreSQL	SERIAL or GENERATED ALWAYS AS IDENTITY
MySQL	AUTO_INCREMENT
SQL Server	IDENTITY(1, 1)
SQLite	INTEGER PRIMARY KEY AUTOINCREMENT

---

Best Practices

✅ Do This:

-- Use IF NOT EXISTS for idempotent scripts
CREATE TABLE IF NOT EXISTS Books (...);                            -- ✅

-- Declare primary keys explicitly
ISBN VARCHAR(20) PRIMARY KEY                                       -- ✅

-- Use NOT NULL for required columns
Title VARCHAR(150) NOT NULL                                        -- ✅

-- Use CHECK constraints for domain rules
Price DECIMAL(10, 2) CHECK (Price >= 0)                            -- ✅

-- Use DEFAULT for columns with sensible defaults
Quantity INT NOT NULL DEFAULT 0                                    -- ✅

-- Name foreign key constraints for clarity
CONSTRAINT fk_loan_member FOREIGN KEY (MemberID) REFERENCES Members(MemberID) -- ✅

❌ Don’t Do This:

-- Don't create a table without a primary key
CREATE TABLE Logs (Message TEXT);  -- no primary key                -- ❌

-- Don't use CHAR for variable-length data
Title CHAR(150)  -- pads with spaces                               -- ❌

-- Don't use FLOAT for money
Price FLOAT  -- rounding errors                                    -- ❌

-- Don't skip NOT NULL on required columns
Title VARCHAR(150)  -- allows null                                 -- ❌

-- Don't use reserved words as table names
CREATE TABLE Order (...);  -- ORDER is reserved                    -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Table already exists	No IF NOT EXISTS	Add IF NOT EXISTS
Foreign key violation	Referenced table not created	Create parent tables first
Composite key wrong	Column-level PK used	Use table-level PRIMARY KEY
Default not applied	Wrong data type	Match default to column type
Reserved word as name	Table name conflicts	Quote the name or rename

---

Real-World Examples

1. Basic Table

CREATE TABLE Movies (title CHAR(20), director CHAR(10));

2. Table with Primary Key

CREATE TABLE Employees (employee_id INT PRIMARY KEY, first_name CHAR(20));

3. Table with NOT NULL

CREATE TABLE Employees (employee_id INT NOT NULL, first_name CHAR(20));

4. Table with Default

CREATE TABLE Employees (salary INT DEFAULT 0);

5. Table with CHECK

CREATE TABLE Employees (age SMALLINT CHECK (age > 0));

6. Table with UNIQUE

CREATE TABLE Members (email VARCHAR(150) UNIQUE);

7. Table with Foreign Key

CREATE TABLE Loans (MemberID INT REFERENCES Members(MemberID));

8. Composite Primary Key

PRIMARY KEY (order_id, product_id)

9. Named Constraint

CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

10. IF NOT EXISTS

CREATE TABLE IF NOT EXISTS Books (...);

---

Visual

The CREATE TABLE Structure

┌──────────────────────────────────────────────┐
│  CREATE TABLE table_name (                   │
│      column1 type constraints,               │
│      column2 type constraints,               │
│      ...                                     │
│      table_constraints                       │
│  );                                          │
│                                              │
│  Column definitions: name, type, constraints │
│  Table constraints: composite keys, named FKs│
│                                              │
└──────────────────────────────────────────────┘

The Column Constraints

┌──────────────────────────────────────────────┐
│  COLUMN CONSTRAINTS                          │
│                                              │
│  NOT NULL:                                   │
│    └─ Value must be provided                 │
│                                              │
│  PRIMARY KEY:                                │
│    └─ Unique identifier                      │
│                                              │
│  UNIQUE:                                     │
│    └─ No duplicates                          │
│                                              │
│  CHECK:                                      │
│    └─ Condition must be true                 │
│                                              │
│  DEFAULT:                                    │
│    └─ Value when none provided               │
│                                              │
└──────────────────────────────────────────────┘

The Referential Actions

┌──────────────────────────────────────────────┐
│  REFERENTIAL ACTIONS                         │
│                                              │
│  ON DELETE CASCADE:                          │
│    └─ Delete referencing rows                │
│                                              │
│  ON DELETE RESTRICT:                         │
│    └─ Reject the delete                      │
│                                              │
│  ON DELETE SET NULL:                         │
│    └─ Set foreign key to null                │
│                                              │
│  ON UPDATE CASCADE:                          │
│    └─ Propagate the update                   │
│                                              │
└──────────────────────────────────────────────┘

The Schema Design Flow

┌──────────────────────────────────────────────┐
│  SCHEMA DESIGN                               │
│                                              │
│  1. Identify entities                        │
│     └─ Books, Members, Loans                 │
│                                              │
│  2. Define columns and types                 │
│     └─ ISBN VARCHAR(20), Title VARCHAR(150)  │
│                                              │
│  3. Declare primary keys                     │
│     └─ ISBN PRIMARY KEY                      │
│                                              │
│  4. Declare foreign keys                     │
│     └─ Loans.MemberID → Members.MemberID     │
│                                              │
│  5. Add constraints                          │
│     └─ CHECK, NOT NULL, DEFAULT              │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Statement	CREATE TABLE
Purpose	Define a new table
Column constraints	NOT NULL, PRIMARY KEY, UNIQUE, CHECK, DEFAULT
Table constraints	Composite PK, named FK, multi-column CHECK
Referential actions	CASCADE, RESTRICT, SET NULL
Idempotent create	IF NOT EXISTS
Auto-increment	SERIAL, AUTO_INCREMENT, IDENTITY, AUTOINCREMENT
Temporary table	CREATE TEMPORARY TABLE

Key takeaways:

• CREATE TABLE defines the structure of a table. The table name, the columns, their data types, and the constraints are declared in a single statement. The database enforces the structure on every subsequent insert and update .
• Column constraints apply to the column they are attached to. NOT NULL requires a value. PRIMARY KEY uniquely identifies each row. UNIQUE prevents duplicates. CHECK validates a condition. DEFAULT supplies a value when none is provided .
• Table constraints apply to multiple columns. A composite primary key is declared at the table level. A named foreign key is declared at the table level. A multi-column check constraint is declared at the table level .
• Foreign keys connect tables and enforce referential integrity. The REFERENCES clause specifies the parent table and column. The ON DELETE and ON UPDATE actions determine what happens when the parent row is modified .
• The auto-increment syntax varies by database. PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY. MySQL uses AUTO_INCREMENT. SQL Server uses IDENTITY. SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT .
• The IF NOT EXISTS clause prevents errors. It makes the statement idempotent, which is useful in scripts that may run multiple times.
• The table structure is fixed after creation. Changing the structure requires an ALTER TABLE statement. The CREATE TABLE statement is the initial contract.

Remember: CREATE TABLE is the first DDL statement most developers write. It defines the table name, the columns, the data types, and the constraints. The constraints are the rules that the database enforces. The foreign keys are the connections to other tables. The default values fill in the gaps. The IF NOT EXISTS clause makes the statement safe to run repeatedly. The table is the foundation of the relational model. Everything else is built on it.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!