- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
SQL CREATE TABLE Statement: Syntax, Usage, and Examples
The SQL 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 SQL
is one of the foundational operations in relational database design and is supported across all major database engines.
How to Use SQL 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 SQL
The create table in sql
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 temp table sql
is perfect for short-term analytical needs or ETL processes.
Examples of SQL 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 from select sql
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 sql 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 in SQL 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.
SQL 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 SQL 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 database table sql
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 SQL 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 SQL
gives you precise control over how your data is stored. Mastering it—and variations like create table from select sql
or create sql table syntax
with keys and defaults—equips you to structure databases that are both reliable and scalable.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.