Creating Tables

Learn how to create tables with columns, data types, and constraints.

18 min read
Beginner

Introduction to Creating Tables

So far, you've learned to query and modify existing tables. But where do tables come from? How do you create them in the first place?

Creating tables is a fundamental skill in SQL - it's how you design the structure of your database. When you create a table, you're defining:

  • What columns exist
  • What type of data each column can hold
  • What rules/constraints apply to the data

Think of it like creating a form or spreadsheet - you define the fields and their properties before filling in the data.

A SQL statement that defines a new table's structure, including column names, data types, and constraints. Once created, the table is empty and ready to accept data via INSERT statements.

Real-world analogy: Creating a table is like designing a form:

  • Column names = form field labels ("Name", "Email", "Age")
  • Data types = input types (text field, number field, date picker)
  • Constraints = validation rules (required fields, unique values, etc.)

Basic CREATE TABLE Syntax

Syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints
);

Let's create a simple employees table:

sql
CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Breaking it down:

  1. CREATE TABLE employees - creates a new table named "employees"
  2. Inside parentheses: define columns
  3. Each column has:
    • Name (id, name, salary, hire_date)
    • Data type (INT, VARCHAR(100), DECIMAL(10,2), DATE)

Common data types recap:

  • INT - whole numbers (1, 42, -100)
  • VARCHAR(n) - variable-length text up to n characters
  • DECIMAL(p, s) - precise decimal numbers (p = total digits, s = decimal places)
  • DATE - calendar dates (YYYY-MM-DD)

Viewing Table Structure

After creating a table, how do you verify its structure? Use DESCRIBE (MySQL) or \d (PostgreSQL):

sql
-- View table structure (MySQL/MariaDB)
DESCRIBE employees;

This shows:

  • Field: column name
  • Type: data type
  • Null: whether NULL values are allowed (YES = allowed)
  • Key: index information (we'll add a PRIMARY KEY soon)
  • Default: default value if none provided
  • Extra: additional properties (like AUTO_INCREMENT)

Adding a Primary Key

A primary key uniquely identifies each row in a table. Every table should have one!

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Now let's check the structure:

sql
DESCRIBE employees;

Notice:

  • Null for id is now NO (primary keys can't be NULL)
  • Key for id shows PRI (primary key indicator)

Primary key rules:

  • Must be unique for each row
  • Cannot contain NULL values
  • Each table should have exactly one primary key
  • Often named id, but any column name works

AUTO_INCREMENT for Automatic IDs

Manually assigning ID values is tedious. Let the database do it automatically!

sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Now when you insert data, the ID auto-increments:

sql
INSERT INTO employees (name, salary, hire_date)
VALUES
    ('Alice Smith', 95000, '2022-01-15'),
    ('Bob Johnson', 87000, '2022-03-20'),
    ('Carol White', 105000, '2021-11-10');

SELECT * FROM employees;

The id values (1, 2, 3) were generated automatically! You didn't have to specify them.

Note: In PostgreSQL, use SERIAL instead of INT AUTO_INCREMENT:

id SERIAL PRIMARY KEY

NOT NULL Constraint

Use NOT NULL to require that a column always has a value:

sql
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT
);
sql
-- This works fine
INSERT INTO products (product_name, price, stock_quantity)
VALUES ('Laptop', 999.99, 50);

-- This fails because product_name is NOT NULL
INSERT INTO products (price, stock_quantity)
VALUES (299.99, 100);

When to use NOT NULL:

  • Essential fields that every record must have (product name, customer email)
  • Fields needed for business logic
  • Foreign keys that reference other tables

When to allow NULL:

  • Optional fields (middle name, phone number)
  • Fields that might not apply to all records

DEFAULT Values

Specify default values for columns when no value is provided:

sql
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    in_stock BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
sql
-- Insert without specifying defaults
INSERT INTO products (product_name, price)
VALUES ('Wireless Mouse', 29.99);

SELECT * FROM products;

Default values automatically applied:

  • stock_quantity = 0
  • in_stock = TRUE (shown as 1)
  • created_at = current timestamp

Common default values:

  • Numbers: 0, -1
  • Booleans: TRUE, FALSE
  • Dates: CURRENT_DATE, CURRENT_TIMESTAMP
  • Strings: 'Unknown', 'N/A'

CHECK Constraints

Use CHECK to enforce rules on column values:

sql
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);
sql
-- This works
INSERT INTO products (product_name, price, discount_percent)
VALUES ('Keyboard', 79.99, 15);

-- This fails: price must be > 0
INSERT INTO products (product_name, price)
VALUES ('Free Sample', 0);

CHECK constraints ensure data validity:

  • price > 0 - no negative or zero prices
  • discount_percent BETWEEN 0 AND 100 - valid percentage range
  • stock_quantity >= 0 - can't have negative inventory
  • age >= 18 - enforce minimum age
  • salary > 0 - positive salaries only

UNIQUE Constraint

UNIQUE ensures no duplicate values in a column (like email addresses or usernames):

sql
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
sql
-- First insert works
INSERT INTO users (username, email, password_hash)
VALUES ('alice_smith', 'alice@example.com', 'hashed_password_123');

-- Second insert fails: duplicate username
INSERT INTO users (username, email, password_hash)
VALUES ('alice_smith', 'alice2@example.com', 'hashed_password_456');

Common UNIQUE columns:

  • Usernames
  • Email addresses
  • Social security numbers
  • Phone numbers
  • SKU/product codes

UNIQUE vs PRIMARY KEY:

  • Table can have multiple UNIQUE columns, but only one PRIMARY KEY
  • UNIQUE columns can be NULL (unless also marked NOT NULL)
  • PRIMARY KEY is automatically NOT NULL and UNIQUE

Complete Example: Orders Table

Let's create a realistic orders table with multiple constraints:

sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    shipping_address VARCHAR(500) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
sql
DESCRIBE orders;

Features of this table:

  • order_id auto-increments as primary key
  • customer_id required (will link to customers table later)
  • order_date defaults to today's date
  • total_amount must be non-negative
  • status defaults to 'pending'
  • created_at records when order was created
  • updated_at auto-updates when row changes

CREATE TABLE IF NOT EXISTS

Avoid errors when running scripts multiple times:

sql
-- First run: creates table
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);
sql
-- Second run: does nothing, no error
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

Best practice: Always use IF NOT EXISTS in scripts and automation to make them idempotent (safe to run multiple times without errors).

Creating Tables from Queries

Create a new table from query results using CREATE TABLE AS:

sql
-- Create summary table from existing data
CREATE TABLE high_earners AS
SELECT
    name,
    salary,
    hire_date
FROM employees
WHERE salary > 90000;
sql
SELECT * FROM high_earners;

Use cases:

  • Create reporting tables
  • Archive old data
  • Create temporary analysis tables
  • Back up subset of data

Note: The new table gets column names and data types from the query, but NOT constraints like PRIMARY KEY or NOT NULL. You'd need to add those separately with ALTER TABLE.

Dropping Tables

Remove a table and all its data with DROP TABLE:

sql
DROP TABLE high_earners;

Danger! DROP TABLE permanently deletes the table and ALL its data. This action cannot be undone. Always:

  1. Back up important data first
  2. Double-check the table name
  3. Consider using IF EXISTS to avoid errors:
DROP TABLE IF EXISTS table_name;

TRUNCATE TABLE

Want to remove all data but keep the table structure? Use TRUNCATE:

sql
-- Remove all rows, keep table structure
TRUNCATE TABLE products;
DROP vs TRUNCATE vs DELETE
Command
Table Structure
Data
Speed
Rollback?
Resets AUTO_INCREMENT?
DROP TABLEDeletedDeletedFastNoYes (table gone)
TRUNCATE TABLEKeptDeletedFastNo (usually)Yes
DELETE FROMKeptDeletedSlowYesNo

When to use each:

  • DROP TABLE - removing table permanently
  • TRUNCATE TABLE - quickly clearing all data for fresh start
  • DELETE FROM - removing specific rows with WHERE clause

Viewing All Tables

List all tables in your database:

sql
-- MySQL/MariaDB
SHOW TABLES;

PostgreSQL equivalent:

\dt

SQL Server:

SELECT name FROM sys.tables;

Common Mistakes to Avoid

Don't make these mistakes:

  1. No primary key: ❌

    • Always define a PRIMARY KEY for each table
    • Usually an auto-incrementing ID column

  2. Wrong data types: ❌

    • Don't use VARCHAR for numbers or dates
    • Don't use INT for decimal values (use DECIMAL)
    • Don't use TEXT when VARCHAR(n) is sufficient

  3. Missing NOT NULL constraints: ❌

    • Essential fields should be NOT NULL
    • Forgetting this allows incomplete data

  4. No defaults for optional fields: ❌

    • Set sensible defaults (0, FALSE, CURRENT_TIMESTAMP)
    • Makes inserts easier and data more consistent

  5. Forgetting IF NOT EXISTS: ❌

    • Scripts fail on second run
    • Use CREATE TABLE IF NOT EXISTS

  6. Too long VARCHAR lengths: ⚠️

    • VARCHAR(255) for everything wastes space
    • Choose appropriate lengths (50 for names, 100 for emails)

  7. Missing CHECK constraints: ❌

    • Let the database enforce rules (price > 0)
    • Don't rely only on application validation

  8. Using reserved keywords as names: ❌

    • Avoid: order, user, select, table
    • Use: orders, users, etc.

Best Practices for Table Design

Follow these principles:

  1. Naming conventions:

    • Use lowercase with underscores: order_items, customer_addresses
    • Plural for table names: employees, products
    • Singular for column names: employee_name, product_id

  2. Always include:

    • Primary key (usually id INT PRIMARY KEY AUTO_INCREMENT)
    • Timestamps (created_at, updated_at)
    • NOT NULL on required fields

  3. Data integrity:

    • Use CHECK constraints for valid ranges
    • Use UNIQUE for columns that shouldn't duplicate
    • Set appropriate DEFAULT values

  4. Performance:

    • Choose smallest data type that fits (INT vs BIGINT)
    • Use VARCHAR instead of TEXT when possible
    • Consider future indexes (we'll learn about these later)

  5. Documentation:

    • Use clear, descriptive column names
    • Add comments if needed (some databases support this)

Key Takeaways

What you learned:

✅ CREATE TABLE defines a new table's structure ✅ Each column needs a name and data type ✅ PRIMARY KEY uniquely identifies each row ✅ AUTO_INCREMENT (or SERIAL) auto-generates IDs ✅ NOT NULL requires a column to have a value ✅ DEFAULT sets automatic values ✅ UNIQUE prevents duplicate values ✅ CHECK enforces validation rules ✅ IF NOT EXISTS prevents errors on re-run ✅ DROP TABLE permanently deletes tables ✅ TRUNCATE TABLE removes all data but keeps structure ✅ DESCRIBE (or \d) shows table structure

Coming up next: We'll learn about foreign keys to link tables together and string functions to manipulate text data!

Practice Exercise: Create these tables with appropriate constraints:

  1. customers - id (PK), name (required), email (unique, required), phone, created_at
  2. blog_posts - id (PK), title (required), content (required), author_id (required), published_date (defaults to today), view_count (defaults to 0)
  3. inventory - product_id (PK), name (required, unique), quantity (required, >= 0), price (required, > 0), last_updated (auto-updates)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.