SQL CREATE TABLE with Data Types and Constraints

SQL CREATE TABLE with Data Types and Constraints

The CREATE TABLE statement defines the structure of a new table, including its columns, data types, and constraints that enforce data integrity.

Basic Syntax

CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  department  VARCHAR(50) DEFAULT 'Unassigned',
  salary      DECIMAL(10, 2) CHECK (salary >= 0),
  hire_date   DATE NOT NULL DEFAULT CURRENT_DATE,
  manager_id  INTEGER REFERENCES employees(id),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Common Data Types

Type	Use Case
INTEGER / BIGINT	Whole numbers, IDs
DECIMAL(p,s)	Exact numbers (money)
VARCHAR(n)	Variable-length strings
TEXT	Unlimited length strings
BOOLEAN	True/false values
DATE / TIMESTAMP	Date and time values
UUID	Universally unique identifiers
JSONB	Semi-structured data (PostgreSQL)

Inline vs Table-Level Constraints

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  price      DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id),
  UNIQUE (order_id, product_id)
);

Temporary Tables

CREATE TEMPORARY TABLE temp_results AS
SELECT * FROM large_table WHERE condition = true;

Best Practices

• Always define a PRIMARY KEY (prefer surrogate keys for simplicity)
• Use NOT NULL unless the column genuinely allows missing values
• Choose the smallest appropriate data type to save storage and improve performance
• Use DECIMAL for financial data (never FLOAT or DOUBLE)
• Add CHECK constraints to enforce business rules at the database level
• Define foreign keys to maintain referential integrity
• Name constraints explicitly for easier debugging and migration management

A well-designed CREATE TABLE statement is the foundation of a reliable, performant database schema.