Primary Keys and Column Types

Primary keys

A primary key uniquely identifies a row.

Most tables use an auto-incrementing integer id:

python

id = db.Column(db.Integer, primary_key=True)

Common column types

• db.Integer
• db.String(length)
• db.Text
• db.Boolean
• db.DateTime
• db.Float
• db.Numeric

Example:

python

created_at = db.Column(db.DateTime, nullable=False)

Constraints

• nullable=False (required)
• unique=True (unique constraint)
• index=True (create an index for faster lookups)

Example:

python

email = db.Column(db.String(120), unique=True, index=True, nullable=False)

Defaults

You can set default values:

python

is_active = db.Column(db.Boolean, default=True, nullable=False)

For timestamps, you typically use a callable (not a fixed time):

python

import datetime
created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow, nullable=False)

Foreign keys (preview)

Relationships depend on foreign keys:

python

user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)

We’ll build relationships later in this phase.

The type annotation decides nullability

In SQLAlchemy 2.0 style, Mapped[T] is not documentation — the compiler of your schema reads it:

models.py

class Note(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]                    # NOT NULL
    body: Mapped[str | None]              # nullable
    views: Mapped[int] = mapped_column(default=0)

Measured schema:

column	type	nullable	primary key
id	INTEGER	no	yes
title	VARCHAR	no	no
body	VARCHAR	yes	no
views	INTEGER	no	no

diagram Diagram mermaid

  flowchart LR
  A["Mapped[str]"] --> N["NOT NULL"]
  B["Mapped[str | None]"] --> Y["NULL allowed"]
  C["mapped_column(primary_key=True)"] --> P["PRIMARY KEY
implies NOT NULL and unique"]

Adding | None is the whole difference between a column that must have a value and one that may not. It is easy to miss in review, and the database will enforce whichever you wrote.

Primary keys

An integer primary key is assigned by the database, which is why it does not exist until the row is written:

pk.py

u = User(name="ada", age=36)
db.session.add(u)
print(u.id)          # None    <- nothing has been INSERTed yet
db.session.commit()
print(u.id)          # 1       <- the database assigned it

Measured exactly that. Code that needs the id — to build a URL, to write a related row — must commit (or flush()) first.

Types worth choosing deliberately

SQLAlchemy	use for	note
String(n)	short text	the length is enforced by most databases, ignored by SQLite
Text	long text	no length limit
Integer	counts, ids
Numeric(10, 2)	money	exact decimal arithmetic
Float	measurements	binary floating point, do not use for currency
Boolean	flags	stored as an integer in SQLite
DateTime(timezone=True)	timestamps	store UTC, convert on display

Never store money in a Float

Float is binary floating point: 0.1 + 0.2 is not 0.3. Use Numeric(10, 2), which maps to an exact decimal type and returns Decimal in Python. A rounding error in a currency column is a bug you cannot fix retrospectively, because the wrong value is what was saved.

String(20) is not enforced by SQLite

SQLite treats the length as advisory, so a 500-character name inserts happily in development and fails in PostgreSQL in production. Validate lengths in your form as well as declaring them in the model.

Constraints belong in the database

constraints.py

name: Mapped[str] = mapped_column(sa.String(20), unique=True)

measured, inserting a duplicate

IntegrityError: UNIQUE constraint failed: user.name

An application check (“does this name exist?”) races with other requests — two can both find nothing and both insert. The database constraint is the only check that cannot be raced. Keep the friendly check for a good error message, and let the constraint be the guarantee.

See it move

sketch What the annotation compiles to p5.js Pause Restart

Mapped[T] is NOT NULL, Mapped[T | None] is nullable, and a primary key is assigned by the database on commit.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. What is the difference between title: Mapped[str] and body: Mapped[str | None]?

   A nothing; the annotation is only for type checkers B the first compiles to NOT NULL and the second allows NULL C the second creates an index D the first is a primary key
   pch.quizShowAnswer

   B — the first compiles to NOT NULL and the second allows NULL — Measured: title came out nullable=False and body nullable=True. The annotation drives the schema, so adding or omitting | None changes what the database enforces.

2. After db.session.add(u) but before commit, what is u.id for an integer primary key?

   A 0 B None, because no INSERT has run and the database assigns the value C a temporary negative number D it raises AttributeError
   pch.quizShowAnswer

   B — None, because no INSERT has run and the database assigns the value — Measured None before commit and 1 after. Anything needing the id — a URL, a related row — must commit or flush first.

3. Why store a currency amount in Numeric(10, 2) rather than Float?

   A Numeric is faster B Float is binary floating point and cannot represent decimal fractions exactly, so amounts drift C Float has a smaller maximum value D Numeric automatically formats the currency symbol
   pch.quizShowAnswer

   B — Float is binary floating point and cannot represent decimal fractions exactly, so amounts drift — 0.1 + 0.2 is not 0.3 in binary floating point. Numeric maps to an exact decimal type and returns Decimal in Python. A rounding error in stored money cannot be corrected after the fact.

4. Why rely on a UNIQUE constraint rather than checking for an existing row first?

   A the check is slower B two concurrent requests can both find nothing and both insert; only the database constraint cannot be raced C the constraint gives a friendlier error message D application checks do not work with SQLite
   pch.quizShowAnswer

   B — two concurrent requests can both find nothing and both insert; only the database constraint cannot be raced — Measured the constraint firing as IntegrityError: UNIQUE constraint failed: user.name. Keep the application check for a good message, and let the constraint be the guarantee.