SQL Create Table

The CREATE TABLE statement is used to create a new relational table in a database. A table is the fundamental data structure in an RDBMS, organizing data into rows (records) and columns (attributes).

When creating a table, you define the table name, column names, column data types, and optional integrity constraints (such as PRIMARY KEY, NOT NULL, or UNIQUE).

Basic Syntax

The standard ANSI SQL syntax for defining a table structure requires comma-separated column definitions enclosed in parentheses:

CREATE TABLE table_name (
    column1_name data_type constraint,
    column2_name data_type constraint,
    column3_name data_type constraint,
    ...
    table_constraints
);

Comprehensive Example

Consider creating a Customers table designed to store registered user profiles for an e-commerce platform:

CREATE TABLE Customers (
    CustomerID      INT AUTO_INCREMENT PRIMARY KEY,
    FirstName       VARCHAR(50) NOT NULL,
    LastName        VARCHAR(50) NOT NULL,
    Email           VARCHAR(100) NOT NULL UNIQUE,
    Age             INT CHECK (Age >= 18),
    AccountCreated  DATETIME DEFAULT CURRENT_TIMESTAMP,
    IsActive        BOOLEAN DEFAULT TRUE
);

Column Breakdown:

CustomerID: An integer identifier defined as the PRIMARY KEY. Automatically increments for every new record.
**FirstName / LastName**: Variable-length strings up to 50 characters; marked NOT NULL so they cannot be empty.
Email: Enforces both NOT NULL and UNIQUE constraints to guarantee no two customer accounts share an email.
Age: Uses a CHECK constraint to restrict entries to users 18 years or older.
AccountCreated: Defaults to the current system date and timestamp (CURRENT_TIMESTAMP) when omitted from an insert statement.
IsActive: Assigns a default boolean value of TRUE.

Column Constraints Overview

Constraints enforce data integrity rules at the column or table level, preventing invalid data from being inserted:

Constraint	Purpose
`NOT NULL`	Ensures that a column cannot store `NULL` values.
`UNIQUE`	Guarantees that all values in a column are distinct across all rows.
`PRIMARY KEY`	Uniquely identifies each row in a table. Automatically enforces `NOT NULL` and `UNIQUE`.
`FOREIGN KEY`	Establishes a relational link between columns in two tables to maintain referential integrity.
`CHECK`	Validates that all values in a column satisfy a specific logical condition.
`DEFAULT`	Specifies a default value to automatically insert if no value is explicitly supplied.

Creating a Table from an Existing Table

You can create a new table by selecting rows and columns from an existing table using CREATE TABLE ... AS SELECT (CTAS) or SELECT INTO:

1. ANSI / MySQL / PostgreSQL / Oracle (CTAS)

CREATE TABLE Customer_Backups AS
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE IsActive = TRUE;

2. SQL Server Syntax

SELECT CustomerID, FirstName, LastName, Email
INTO Customer_Backups
FROM Customers
WHERE IsActive = 1;

Safe Table Creation (`IF NOT EXISTS`)

To make execution scripts safe to re-run without throwing errors if the target table already exists, use the IF NOT EXISTS clause:

CREATE TABLE IF NOT EXISTS Products (
    ProductID   INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price       DECIMAL(10, 2) NOT NULL
);

Note: Supported in PostgreSQL, MySQL, SQLite, and MariaDB. In SQL Server, table existence checks are typically handled using IF OBJECT_ID('table_name', 'U') IS NULL.

Engine-Specific Auto-Increment Strategies

When defining auto-generating primary keys, different RDBMS engines utilize distinct syntax implementations:

-- MySQL / MariaDB
CustomerID INT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL (ANSI standard)
CustomerID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- PostgreSQL (Legacy short-hand)
CustomerID SERIAL PRIMARY KEY

-- SQL Server (T-SQL)
CustomerID INT IDENTITY(1,1) PRIMARY KEY

-- SQLite
CustomerID INTEGER PRIMARY KEY AUTOINCREMENT