Appendices
Appendix D. SQL Data Types and Constraints
The type and constraint menu of Chapter 9, consolidated for both platforms, with the canonical university column as the example wherever one exists.
Numeric types
| Type | Range / precision | Use | Example |
|---|
TINYINT (MySQL) | −128..127 (255 unsigned) | Tiny flags, ratings | — |
SMALLINT | ±32 thousand | Years, counts that stay small | admission_year, credits |
INTEGER / INT | ±2.1 billion | IDs, counts | student_id, section_id |
BIGINT | ±9.2 quintillion | Large IDs, counters | txn_id in banking |
NUMERIC(p,s) / DECIMAL(p,s) | Exact, p total and s fractional digits | Money, GPA — anything countable | budget NUMERIC(12,2), gpa NUMERIC(3,2) |
REAL / FLOAT / DOUBLE PRECISION | Binary floating point | Measurements, never money | — |
SERIAL (PG legacy) | Integer + sequence shorthand | Auto-increment keys | — |
GENERATED ... AS IDENTITY | Standard auto-increment | New surrogate keys | Chapter 9/15 |
AUTO_INCREMENT (MySQL) | Per-table counter | MySQL surrogate keys | Chapter 9/16 |
BOOLEAN (PG) / TINYINT(1) (MySQL) | TRUE/FALSE/NULL / 0-1 | Flags | active |
Rules: exact numerics for anything aggregated as money or grades; NUMERIC(p,s) scale chosen as a domain rule (GPA NUMERIC(3,2) enforces 0.00–9.99); integers prefer the smallest fitting type; UNSIGNED (MySQL) doubles positive range.
Character and text types
| Type | Notes | Example |
|---|
CHAR(n) | Fixed-length, padded | grade CHAR(2) |
VARCHAR(n) | Variable, length rule | full_name VARCHAR(60), course_id VARCHAR(8) |
TEXT (PG) | Unlimited, preferred default | Descriptions |
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT (MySQL) | Size tiers | Large bodies |
BINARY/VARBINARY/BYTEA/BLOB | Bytes | Files, hashes |
ENUM(...) (MySQL) | Domain-as-type (Chapter 9's caution) | — |
Date and time types
| Type | Meaning | Example |
|---|
DATE | Calendar day | hire_date |
TIME | Clock time | — |
TIMESTAMP / DATETIME | Date + time | recorded_at |
TIMESTAMPTZ (PG) | UTC-stored, zone-rendered | Event instants |
TIMESTAMP (MySQL) | UTC-converted, range 1970–2038 | MySQL event instants |
DATETIME (MySQL) | Literal, range 1000–9999 | Scheduled wall-clock facts |
INTERVAL (PG) / interval syntax (MySQL) | Durations | hire_date + INTERVAL '1 year' |
Other types
| Type | Use |
|---|
JSON / jsonb | Semi-structured payloads (Chapter 15/16/27) |
uuid (PG) / BINARY(16) (MySQL) | Distributed identifiers |
inet (PG) | IP addresses |
Arrays int[], ranges tstzrange, enums, domains (PG) | Chapter 15's native toolbox |
vector(n) (pgvector) | Embeddings (Chapter 27) |
Constraints
| Constraint | Enforces | Canonical example |
|---|
NOT NULL | Value always known | full_name NOT NULL |
PRIMARY KEY | Identity: unique + not null (+ clustered in InnoDB) | student_id, (student_id, section_id) |
UNIQUE | Natural identity, alternate keys | dept_name UNIQUE |
FOREIGN KEY ... REFERENCES | Values exist in the parent | every FK arrow on the ERD |
ON DELETE CASCADE / RESTRICT / SET NULL / SET DEFAULT | Parent-removal policy | Chapter 6/9 |
CHECK (cond) | Domain rules (NULL passes — add OR col IS NULL where absence is legal) | CHECK (gpa BETWEEN 0.00 AND 4.00) |
DEFAULT expr | Insert-time fill | capacity DEFAULT 40 |
DEFERRABLE INITIALLY DEFERRED (PG) | Check at COMMIT | Chapter 15/22 |
NOT VALID + VALIDATE (PG) | Online constraint add | Chapter 15 |
EXCLUDE USING gist (...) (PG) | Non-overlap / complex conflicts | Chapter 29 (hotel bookings) |
| Generated columns | Computed stored values | Chapter 15 |
NULL rules (summary)
NULL means "value absent," not zero or empty string. Comparisons with NULL are UNKNOWN; WHERE keeps only TRUE. Aggregates skip NULLs except COUNT(*). IS NULL / IS NOT NULL are the tests; COALESCE, NULLIF, IS DISTINCT FROM (and MySQL's <=>) are the tools (Chapters 2, 10, 11).