SQL CREATE TABLE

SQL
POSTGRES
INTERACTIVE

Complete SQL CREATE TABLE Technical Guide

1. Fundamental Syntax 
CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [table_options];
  1. Column definitions: Name, data type, and optional constraints
  2. Table constraints: PRIMARY KEY, FOREIGN KEY, CHECK, etc.
  3. Table options: Engine-specific settings (storage, partitioning, etc.)
2. Data Types Deep Dive
Numeric
  1. INT, BIGINT
  2. DECIMAL(10,2)
  3. FLOAT, DOUBLE
  4. SERIAL (auto-increment)
Text/Temporal
  1. VARCHAR(255)
  2. TEXT
  3. DATE, TIMESTAMP
  4. INTERVAL
Specialized
  1. JSON/JSONB
  2. ARRAY
  3. UUID
  4. GIS types (POINT, POLYGON)
3. Constraint Types
Column-level
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18)
);
Table-level
CREATE TABLE orders (
    order_id INT,
    user_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    CHECK (total > 0)
);
4. Advanced Table Features
Generated Columns
CREATE TABLE products (
    price DECIMAL(10,2),
    tax DECIMAL(10,2) GENERATED ALWAYS AS (price * 0.1) STORED
);
Partitioning
CREATE TABLE logs (
    log_date DATE,
    message TEXT
) PARTITION BY RANGE (log_date);
Temporal Tables
CREATE TABLE employees (
    /* columns */
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH SYSTEM VERSIONING;
Compression
CREATE TABLE archive (
    /* columns */
) COMPRESSION = 'ZLIB';
5. Database-Specific Features
MySQL/InnoDB
CREATE TABLE invoices (
    /* columns */
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci
  ROW_FORMAT=COMPRESSED;
PostgreSQL
CREATE TABLE spatial_data (
    location GEOGRAPHY(POINT),
    name TEXT
) WITH (fillfactor=70);
SQL Server
CREATE TABLE documents (
    content VARBINARY(MAX),
    INDEX idx_compressed 
      COMPRESSED WITH (DATA_COMPRESSION = PAGE)
) FILESTREAM_ON filegroup;
Oracle
CREATE TABLE ledger (
    transaction_id RAW(16) DEFAULT SYS_GUID(),
    amount NUMBER(19,4),
    CONSTRAINT pk_ledger PRIMARY KEY (transaction_id)
) ORGANIZATION INDEX;
6. Performance & Design Best Practices
  1. Column Order: Place frequently accessed columns first
  2. Data Types: Choose the smallest appropriate type
  3. NULL Handling: Consider NOT NULL where appropriate
  4. Default Values: Optimize with sensible defaults
  5. Storage Parameters: Tune fillfactor, compression, etc.
Table Design Checklist
/*
[ ] Primary key defined
[ ] Appropriate data types
[ ] Essential constraints
[ ] Indexes planned
[ ] Storage requirements estimated
[ ] Partitioning strategy considered
[ ] Security requirements implemented
*/

Advanced CREATE TABLE Topics

1. Temporary Tables 
-- Session-scoped temporary table
CREATE TEMPORARY TABLE session_cache (
    id SERIAL PRIMARY KEY,
    data JSON NOT NULL
) ON COMMIT PRESERVE ROWS;

-- Transaction-scoped temporary table
CREATE TEMPORARY TABLE temp_results (
    calculation_id INT,
    result DECIMAL(16,4)
) ON COMMIT DELETE ROWS;
  1. Visibility: Only visible to the creating session
  2. Lifespan: Session-bound or transaction-bound
  3. Storage: Typically in memory or tempdb space
  4. Indexing: Can have indexes but with performance tradeoffs
2. CTAS Patterns 
-- Basic CTAS with data copy
CREATE TABLE customer_archive AS
SELECT * FROM customers 
WHERE status = 'inactive';

-- Schema-only copy with enhanced structure
CREATE TABLE optimized_orders AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE 1=0 WITH NO DATA;

-- Cross-database CTAS
CREATE TABLE reporting.sales_summary AS
SELECT product_id, SUM(quantity) 
FROM operational.sales
GROUP BY product_id;
  1. Data Loading: Can include or exclude data
  2. Schema Inference: Derives structure from query
  3. Performance: Often faster than CREATE + INSERT
  4. Limitations: May not copy constraints/indexes
3. Table Cloning Techniques 
-- Oracle complete clone
CREATE TABLE employees_clone 
AS SELECT * FROM employees 
WHERE 1=1;

-- PostgreSQL LIKE clause
CREATE TABLE new_logs (LIKE logs INCLUDING ALL);

-- SQL Server full schema copy
SELECT * INTO orders_backup 
FROM orders
WHERE 1=0;
  1. Schema-Only: Clone structure without data
  2. Deep Clone: Copy constraints, indexes, defaults
  3. Partial Clone: Select specific columns/features
  4. Storage Parameters: May or may not be inherited
4. System Catalog Impact 
-- PostgreSQL catalog queries
SELECT * FROM pg_class 
WHERE relname = 'new_table';

-- SQL Server metadata
SELECT * FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'CustomerData';
  1. Metadata Storage: How table definitions are recorded
  2. Dependencies: Views, functions, and triggers referencing the table
  3. Statistics: Initial statistics collection behavior
  4. DDL Triggers: System-wide triggers on table creation
5. DDL Triggers 
-- SQL Server DDL trigger
CREATE TRIGGER audit_table_creations
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    INSERT INTO ddl_audit_log
    SELECT EVENTDATA(), SYSTEM_USER, GETDATE();
END;

-- Oracle event trigger
CREATE TRIGGER validate_table_name
BEFORE CREATE ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        -- Validate naming conventions
    END IF;
END;
  1. Event Capture: Logging table creation events
  2. Prevention: Blocking invalid table creations
  3. Security: Enforcing naming standards
  4. Performance Impact: Overhead of trigger execution
6. Cross-Database Syntax Comparison 
/* MySQL vs PostgreSQL vs SQL Server */
-- Auto-increment variations:
-- MySQL
CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);

-- PostgreSQL
CREATE TABLE t1 (id SERIAL PRIMARY KEY);

-- SQL Server
CREATE TABLE t1 (id INT IDENTITY(1,1) PRIMARY KEY);

-- Oracle
CREATE TABLE t1 (id NUMBER GENERATED ALWAYS AS IDENTITY);
  1. Data Type Mappings: Equivalent types across platforms
  2. Constraint Syntax: Different keyword requirements
  3. Storage Parameters: Engine-specific table options
  4. Default Behaviors: Nullability, character sets, collations