SQL - CREATE TABLE Statement
• The CREATE TABLE statement defines both the table structure and data integrity rules through column definitions, data types, and constraints that enforce business logic at the database level
Key Insights
• The CREATE TABLE statement defines both the table structure and data integrity rules through column definitions, data types, and constraints that enforce business logic at the database level • Primary keys, foreign keys, and check constraints should be defined during table creation rather than added later to ensure data consistency from the start • Modern SQL supports advanced features like computed columns, default values, and table-level constraints that reduce application-side validation code
Basic CREATE TABLE Syntax
The CREATE TABLE statement establishes a new table in your database with specified columns and their data types. Every column requires a name and data type at minimum.
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date DATE,
salary DECIMAL(10, 2)
);
This creates a simple table with six columns. The data types determine what values each column accepts: INT for whole numbers, VARCHAR for variable-length strings, DATE for dates, and DECIMAL for precise numeric values with two decimal places.
Column Constraints
Constraints enforce data integrity rules directly in the database schema. Apply them inline with column definitions for single-column constraints.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
salary DECIMAL(10, 2) CHECK (salary > 0),
department_id INT NOT NULL,
manager_id INT
);
NOT NULL prevents null values. UNIQUE ensures no duplicate values exist in that column. PRIMARY KEY combines NOT NULL and UNIQUE while creating an index. DEFAULT provides automatic values when none is specified. CHECK validates data against a boolean expression.
Primary and Foreign Keys
Primary keys uniquely identify each row. Foreign keys establish relationships between tables and maintain referential integrity.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL UNIQUE,
location VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
hire_date DATE NOT NULL,
salary DECIMAL(10, 2),
department_id INT NOT NULL,
manager_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
The foreign key on department_id ensures every employee belongs to a valid department. The self-referencing foreign key on manager_id allows employees to reference other employees as managers.
Composite Primary Keys
When no single column uniquely identifies a row, combine multiple columns into a composite primary key.
CREATE TABLE project_assignments (
employee_id INT,
project_id INT,
assigned_date DATE NOT NULL,
role VARCHAR(50),
hours_allocated DECIMAL(5, 2),
PRIMARY KEY (employee_id, project_id),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
This junction table tracks many-to-many relationships between employees and projects. The composite primary key (employee_id, project_id) ensures an employee can’t be assigned to the same project twice.
Auto-Incrementing Columns
Most databases provide auto-incrementing integer columns for surrogate keys. Syntax varies by database system.
PostgreSQL:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
MySQL:
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SQL Server:
CREATE TABLE customers (
customer_id INT IDENTITY(1,1) PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
created_at DATETIME DEFAULT GETDATE()
);
Check Constraints with Complex Logic
Check constraints can validate data against complex business rules using SQL expressions.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
ship_date DATE,
total_amount DECIMAL(10, 2) NOT NULL,
discount_percent DECIMAL(5, 2) DEFAULT 0,
status VARCHAR(20) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CHECK (ship_date IS NULL OR ship_date >= order_date),
CHECK (total_amount >= 0),
CHECK (discount_percent BETWEEN 0 AND 100),
CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);
These constraints ensure ship dates don’t precede order dates, amounts are non-negative, discounts stay within valid ranges, and status values match predefined options.
Computed Columns
Some databases support computed columns that derive values from other columns automatically.
SQL Server:
CREATE TABLE invoices (
invoice_id INT PRIMARY KEY,
subtotal DECIMAL(10, 2) NOT NULL,
tax_rate DECIMAL(5, 4) NOT NULL,
tax_amount AS (subtotal * tax_rate) PERSISTED,
total_amount AS (subtotal + (subtotal * tax_rate)) PERSISTED
);
PostgreSQL (using generated columns):
CREATE TABLE invoices (
invoice_id INT PRIMARY KEY,
subtotal DECIMAL(10, 2) NOT NULL,
tax_rate DECIMAL(5, 4) NOT NULL,
tax_amount DECIMAL(10, 2) GENERATED ALWAYS AS (subtotal * tax_rate) STORED,
total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (subtotal + (subtotal * tax_rate)) STORED
);
Computed columns eliminate redundant data storage and ensure calculations remain consistent.
Conditional Table Creation
Use IF NOT EXISTS to avoid errors when a table already exists, useful in deployment scripts.
PostgreSQL and MySQL:
CREATE TABLE IF NOT EXISTS audit_log (
log_id SERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
operation VARCHAR(10) NOT NULL,
user_name VARCHAR(100) NOT NULL,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
old_values JSONB,
new_values JSONB
);
SQL Server:
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'audit_log')
BEGIN
CREATE TABLE audit_log (
log_id INT IDENTITY(1,1) PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
operation VARCHAR(10) NOT NULL,
user_name VARCHAR(100) NOT NULL,
timestamp DATETIME DEFAULT GETDATE(),
old_values NVARCHAR(MAX),
new_values NVARCHAR(MAX)
);
END
Creating Tables from Query Results
Generate new tables directly from SELECT query results.
CREATE TABLE high_value_customers AS
SELECT
c.customer_id,
c.customer_name,
c.email,
SUM(o.total_amount) as lifetime_value,
COUNT(o.order_id) as order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.email
HAVING SUM(o.total_amount) > 10000;
This creates a new table with the query’s result set. Note that constraints and indexes from source tables don’t transfer automatically.
Temporary Tables
Temporary tables exist only during a session and automatically drop when the session ends.
CREATE TEMPORARY TABLE session_cart (
cart_item_id INT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Temporary tables are useful for complex multi-step queries, staging data during ETL processes, or storing intermediate results without cluttering the permanent schema.
Index Creation During Table Definition
While indexes can be added later, defining them during table creation documents performance-critical access patterns.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
sku VARCHAR(50) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(category_id),
INDEX idx_category (category_id),
INDEX idx_price (price),
INDEX idx_created (created_at)
);
The PRIMARY KEY and UNIQUE constraints automatically create indexes. Additional indexes on category_id, price, and created_at optimize common query patterns.