Home/Learn/SQL/CREATE TABLE, Data Types & Constraints

CREATE TABLE, Data Types & Constraints

Beginner

Schema & DDL

CREATE TABLE defines a table's structure, column data types, and integrity constraints like NOT NULL, UNIQUE, DEFAULT, and CHECK.

Overview

DDL (Data Definition Language) statements define the schema. CREATE TABLE specifies columns with their data types and optional inline constraints. NOT NULL prevents empty values, UNIQUE enforces distinct values per column, DEFAULT supplies a fallback when no value is inserted, and CHECK validates that inserted/updated values satisfy a boolean expression. The database engine enforces these constraints at write time, rejecting violations before they reach storage. Choosing correct data types (INT vs BIGINT, VARCHAR(n) vs TEXT) directly affects storage size, index performance, and implicit type-cast behaviour during queries.

Basic Table Creation with Constraints

Constraints can be declared inline (column-level) or as separate table-level entries. Table-level syntax is required for multi-column constraints. Always choose the narrowest data type that covers the domain — it reduces storage and improves cache efficiency.

SQL — CREATE TABLE with all constraint types

-- PostgreSQL / MySQL
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,           -- auto-increment PK
    email       VARCHAR(255) NOT NULL UNIQUE,    -- inline NOT NULL + UNIQUE
    username    VARCHAR(50)  NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'active',
    age         SMALLINT     CHECK (age >= 0 AND age <= 150),
    balance     NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Table-level CHECK for multi-column rule
CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    discount     NUMERIC(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT chk_discount CHECK (discount <= total_amount)
);

Common Data Type Choices

Picking the right type avoids silent truncation, unnecessary storage, and index bloat. Use BIGINT for surrogate keys in high-volume tables. Prefer NUMERIC/DECIMAL over FLOAT for money to avoid floating-point rounding errors.

SQL — data type best practices

-- Anti-pattern: VARCHAR(255) everywhere, FLOAT for money
CREATE TABLE products_bad (
    id       INT,
    price    FLOAT,          -- rounding errors: 0.1 + 0.2 != 0.3
    is_active VARCHAR(255)   -- wastes space; use BOOLEAN
);

-- Better
CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    sku         VARCHAR(50)   NOT NULL UNIQUE,
    name        VARCHAR(200)  NOT NULL,
    price       NUMERIC(10,2) NOT NULL,    -- exact decimal arithmetic
    is_active   BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()  -- timezone-aware
);

-- PostgreSQL: use TEXT (no length limit, same storage as VARCHAR)
-- MySQL:      VARCHAR(n) still preferred — TEXT columns can't be fully indexed

Spring Data JPA Equivalent

JPA @Entity maps a Java class to a table. Constraints declared in SQL should be mirrored with Bean Validation annotations so they are caught at the application layer before hitting the database.

Java — JPA entity with Bean Validation

// Java — Spring Data JPA entity matching the users table
@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 255)
    @Email
    private String email;

    @Column(nullable = false, length = 50)
    @NotBlank
    private String username;

    @Column(nullable = false, length = 20)
    private String status = "active";

    @Column(nullable = false)
    @DecimalMin("0.00")
    private BigDecimal balance = BigDecimal.ZERO;

    @Column(nullable = false, updatable = false)
    private LocalDateTime createdAt = LocalDateTime.now();
}

Key Points to Remember

1NOT NULL, UNIQUE, DEFAULT, and CHECK are enforced by the DB engine at write time.
2Use NUMERIC/DECIMAL for monetary values; never FLOAT or DOUBLE.
3BIGSERIAL (PostgreSQL) / BIGINT AUTO_INCREMENT (MySQL) for surrogate PKs in high-volume tables.
4Table-level constraints are required when a constraint spans multiple columns.
5Mirror DB constraints with Bean Validation annotations in JPA to fail fast at the app layer.
6TIMESTAMPTZ (timestamp with time zone) prevents bugs in multi-region deployments.

Interview Questions

What is the difference between a column-level and a table-level constraint?

EasyInfosys

Why should you use NUMERIC instead of FLOAT for storing prices?

EasyFlipkart

How does a CHECK constraint differ from application-level validation?

MediumAmazon

What happens when you insert a row that violates a UNIQUE constraint inside a transaction?

MediumAtlassian

What are the storage and performance implications of choosing VARCHAR(255) vs TEXT in PostgreSQL?

HardGoogle

Ask Aria about CREATE TABLE, Data Types & Constraints

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

All SQL Concepts

Primary Key & Foreign Key Constraints