CREATE TABLE and Data Types

Foundational Table Structure

The CREATE TABLE statement serves as the blueprint for your data, establishing a named container that defines exactly what columns exist and what constraints apply to them. When you define a table, you are essentially telling the database engine how to allocate physical storage and how to enforce business logic at the hardware level. The fundamental syntax requires a unique table name followed by a comma-separated list of column definitions, each paired with a specific data type. By establishing a rigid structure, you guarantee that every row in your database conforms to the same organizational standard, which is critical for predictable query results. Without these explicit definitions, the engine could not optimize indices or manage memory efficiently. Think of this as defining the contract for your data; once the table is initialized, the system strictly enforces this layout to ensure operational stability throughout the lifecycle of the application, preventing malformed data from corrupting your records.

-- Creates a basic table for user accounts
CREATE TABLE users (
    user_id INT, -- Unique identifier for the user
    username VARCHAR(50), -- Variable length string for names
    created_at TIMESTAMP -- Timestamp of record creation
);

Understanding Numeric Data Types

Numeric types are the backbone of quantitative analysis and relational record-keeping, requiring a deep understanding of precision versus storage footprint. Integer types are generally used for discrete counts, identification keys, and quantities where decimals are irrelevant; they offer high performance due to their compact representation in memory. Conversely, fixed-point and floating-point types allow for fractional values, which are essential for financial calculations or scientific measurements. When you choose an integer over a floating-point type, you are choosing strict exactness and faster arithmetic processing. If you select a type with too large a range, you waste storage; if you choose one too small, you encounter overflow errors during runtime. The database engine calculates the storage cost based on the precision and scale assigned to the numeric fields. Properly sizing these fields prevents data truncation and ensures that your analytical queries remain performant even as your datasets grow into the millions of rows, maintaining mathematical integrity.

-- Demonstrating precise and approximate numeric types
CREATE TABLE orders (
    order_id INT, -- Discrete ID, small storage footprint
    price DECIMAL(10, 2), -- Exact for currency, 10 total digits, 2 decimal
    tax_rate FLOAT -- Approximate for scientific, faster for math
);

Textual and Character Data Types

Textual data types allow for the storage of strings, but they differ significantly in how they handle memory allocation. CHAR fields are fixed-length, meaning the engine reserves space for the maximum length regardless of the actual content, which provides fast lookups for fields of constant size like country codes. VARCHAR fields, however, are dynamic, allocating only as much space as the stored data requires plus a small overhead for length tracking. This trade-off between speed and storage is vital when designing tables that store varying amounts of textual input. Using the correct textual type impacts index performance; if your columns are inconsistently defined, the engine cannot utilize memory-resident optimizations efficiently. Furthermore, choosing between these types dictates how the system handles padding and whitespace during comparison operations. You must analyze the variance in your data to decide if the predictability of fixed-width storage outweighs the storage density of variable-width strings in your production environment.

-- Comparing fixed and variable length character types
CREATE TABLE products (
    sku CHAR(10), -- Fixed length for predictable identifiers
    product_name VARCHAR(255), -- Variable for descriptive labels
    description TEXT -- For massive blocks of unstructured text
);

Temporal and Date Types

Handling time is a complex challenge because dates and timestamps are not merely strings; they are high-precision markers that the database engine can perform arithmetic and filtering on natively. When you utilize DATE, TIMESTAMP, or TIME types, you enable the system to perform date-range comparisons, extract specific components like months or days, and manage time zones without manual parsing. Storing time as a string is a common anti-pattern because it prevents the engine from verifying logical correctness, such as preventing an invalid date like February 30th from entering the system. Because these types follow strict ISO standards, the engine can build specialized indices that make date-based filtering incredibly fast. By allowing the engine to handle the temporal logic, you ensure that your code remains readable and robust. Always favor these native types over custom string representations to leverage the engine’s internal optimizations and ensure that your chronological data remains logically consistent across all your analytical reports and system functions.

-- Using date and time types for temporal tracking
CREATE TABLE sessions (
    session_id INT,
    login_date DATE, -- Just the calendar date
    login_time TIME, -- Specific time of day
    last_seen TIMESTAMP -- Exact date and time point
);

Implementing Column Constraints

Constraints are the final layer of protection for your schema, acting as programmatic gatekeepers that prevent invalid data from ever touching your disk. The NOT NULL constraint is perhaps the most critical, as it guarantees that essential fields never contain missing values, simplifying your future queries by removing the need for null-checking logic. PRIMARY KEY constraints establish a unique identity for every record, which the database engine uses to organize internal storage structures, typically via B-trees, to accelerate data retrieval. UNIQUE constraints prevent logical duplicates, ensuring that data points like email addresses remain singular across the entire dataset. By applying these constraints at the time of table creation, you offload the burden of data validation from your application code to the database engine itself. This ensures that regardless of which client or service writes to the table, the integrity of your information is consistently maintained, resulting in a system that is self-documenting and structurally resilient against common data quality issues.

-- Implementing constraints to ensure data integrity
CREATE TABLE employees (
    emp_id INT PRIMARY KEY, -- Forces uniqueness and indexing
    email VARCHAR(100) UNIQUE NOT NULL, -- Ensures no duplicates/nulls
    hire_date DATE DEFAULT CURRENT_DATE -- Auto-populates if missing
);

Interview questions

All SQL interview questions →

Check yourself

Take the full SQL quiz →