SQL CREATE TABLE Statement: Syntax, Usage, and Examples

The CREATE TABLE statement allows you to define and create a new table in a database. You specify the table’s name, its columns, data types, constraints, and relationships. CREATE TABLE is one of the foundational operations in relational database design and is supported across all major database engines.

How to Use CREATE TABLE

The basic syntax for defining a new table looks like this:

CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  ...
);

Each column includes a name, a data type (such as INT, VARCHAR, or DATE), and optionally, constraints like NOT NULL, PRIMARY KEY, or UNIQUE.

Basic Example

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  hire_date DATE
);

This creates an employees table with four columns. The id column serves as the primary key.

When to Use CREATE TABLE

The CREATE TABLE command is used during:

• Initial database setup
• Creating temporary tables for specific queries
• Adding new tables to support features in your application
• Prototyping or data modeling during development

You can use it to define both permanent and temporary structures and apply all necessary constraints up front.

Defining Permanent Structures

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2)
);

This is standard usage for persistent data storage.

Temporary Analysis or Caching

CREATE TEMP TABLE recent_orders (
  order_id INT,
  customer_id INT,
  total DECIMAL(8, 2)
);

CREATE TABLE is perfect for short-term analytical needs or ETL processes.

Examples of CREATE TABLE

Example 1: CREATE TABLE with Constraints

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at DATETIME DEFAULT GETDATE()
);

This version includes a UNIQUE constraint and a DEFAULT timestamp.

Example 2: CREATE TABLE with Foreign Key

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

This establishes a relationship between orders and customers.

Example 3: CREATE TABLE from SELECT

You can create a new table directly from the result of a query:

CREATE TABLE archived_orders AS
SELECT * FROM orders
WHERE order_date < '2023-01-01';

This CREATE TABLE command copies the data structure and contents.

Example 4: SQL Query CREATE TABLE with Primary Key

CREATE TABLE inventory (
  item_id INT NOT NULL,
  warehouse_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY (item_id, warehouse_id)
);

This defines a composite primary key.

Learn More About CREATE TABLE in SQL

SQL Create Table Syntax Deep Dive

The CREATE TABLE syntax supports many features:

• Data types: INT, DECIMAL, CHAR, VARCHAR, TEXT, DATE, DATETIME, BOOLEAN
• Column constraints: NOT NULL, DEFAULT, CHECK, UNIQUE
• Table constraints: PRIMARY KEY, FOREIGN KEY, CONSTRAINT names
• Identity/autoincrement: AUTO_INCREMENT (MySQL), IDENTITY (SQL Server)

Example with multiple constraints:

CREATE TABLE payments (
  payment_id INT IDENTITY PRIMARY KEY,
  amount DECIMAL(10, 2) NOT NULL CHECK (amount > 0),
  status VARCHAR(20) DEFAULT 'Pending'
);

How to Create a Table with Default Values

You can define default values for columns:

CREATE TABLE logs (
  log_id INT PRIMARY KEY,
  event_type VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

This sets created_at to the current time by default.

Creating Temporary Tables

Use TEMP or TEMPORARY to create a table that exists only during the session:

CREATE TEMP TABLE temp_results (
  id INT,
  result VARCHAR(100)
);

Temporary tables are ideal for intermediate calculations and won’t persist beyond the current session or connection.

Create Table with Auto-Increment

In MySQL:

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50)
);

In SQL Server:

CREATE TABLE users (
  user_id INT IDENTITY(1,1) PRIMARY KEY,
  username VARCHAR(50)
);

Auto-increment columns are useful for generating unique IDs automatically.

Create Database Table Script

To organize a database schema, you may write a script to create multiple related tables:

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

This CREATE TABLE block defines and links two related tables.

Conditional Table Creation

To avoid errors, check if the table already exists:

SQL Server:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'projects')
BEGIN
  CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    name VARCHAR(100)
  );
END;

MySQL:

CREATE TABLE IF NOT EXISTS projects (
  project_id INT PRIMARY KEY,
  name VARCHAR(100)
);

Dropping and Recreating Tables

Sometimes you need to drop and recreate tables for updates or resets:

DROP TABLE IF EXISTS temp_results;

CREATE TEMP TABLE temp_results (
  id INT,
  result VARCHAR(255)
);

Useful for dynamic queries and testing.

Naming Conventions

Follow consistent naming for readability and maintainability:

• Use snake_case: user_profiles, order_items
• Prefix foreign keys: customer_id, product_id
• Name junction tables by combining related tables: student_courses

Storing Dates and Timestamps

Use DATE, TIME, or DATETIME for temporal data:

CREATE TABLE events (
  event_id INT PRIMARY KEY,
  title VARCHAR(100),
  event_date DATE,
  event_time TIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Choose the most appropriate type for your application.

The CREATE TABLE command is a foundational building block in every relational database. It allows you to define structure, relationships, and constraints all at once. From designing production schemas to crafting temporary tables for analysis, CREATE TABLE gives you precise control over how your data is stored.