Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 37 ยท Core and Expressions

Column Types, Defaults and Constraints

Pick the right SQLAlchemy column types, set Python and server defaults, and enforce data rules with NOT NULL, UNIQUE, CHECK, foreign keys and indexes.

Columns do more than hold values. A column's type decides how Python values are converted to and from the database. Its defaults fill in values you did not supply, and its constraints make the database itself reject bad data. Pushing rules into the schema is one of the best habits you can build: application code has bugs, but a CHECK constraint is always on.

This lesson walks through the common types, the three kinds of defaults, and every constraint you will use daily.

The types you will use most

SQLAlchemy has generic types that work everywhere and are rendered as the right type for each database.

Type Python value Notes
Integer, BigInteger, SmallInteger int
String(n), Text str String(n) gives VARCHAR(n); Text is unbounded
Numeric(10, 2) decimal.Decimal exact money values
Float float approximate
Boolean bool
Date, DateTime, Time date, datetime, time DateTime(timezone=True) where supported
Enum(MyEnum) an enum.Enum member stored as text (or a native enum)
JSON dict / list native on PostgreSQL, text-based on SQLite
LargeBinary bytes
Uuid uuid.UUID
Python Types convert values both ways

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every value came back as the right Python type, even though SQLite itself only has a handful of storage classes. The type object does the converting, which is why it is worth declaring types precisely.

SQLite does not enforce VARCHAR(n) lengths and stores numbers as floats or integers internally, so a Numeric column on SQLite is only as exact as a float. Server databases like PostgreSQL enforce lengths and store exact decimals.

Three kinds of defaults

Option Who computes it When
default= Python (SQLAlchemy) on INSERT, if you did not give a value
server_default= the database in the CREATE TABLE, also applies to raw SQL
onupdate= Python on every UPDATE that does not set the column

A default can be a plain value or a callable such as datetime.datetime.now. A server_default is SQL text or an expression: server_default=func.now() or server_default="0".

Python default, server_default and onupdate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The raw SQL insert got state as NULL (only the Python-side default is missing there) but priority as 3, because the server default lives in the table definition. This is why a server_default is the safer choice for values that must always exist, and why default only helps when inserts go through SQLAlchemy. The onupdate ran only for the update that went through the Core update().

Constraints

Constraints are declared as arguments to Table, or with shortcuts on Column.

Constraint Shortcut or class Purpose
NOT NULL nullable=False a value is required
PRIMARY KEY primary_key=True, PrimaryKeyConstraint unique row identity
UNIQUE unique=True, UniqueConstraint(...) no duplicates (also across several columns)
CHECK CheckConstraint("qty >= 0") a rule on values
FOREIGN KEY ForeignKey("t.id", ondelete="CASCADE") reference another table
INDEX index=True, Index(...) speed up lookups
Python The database rejects bad rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every bad insert raised IntegrityError, whose .orig attribute holds the driver's own message. The cascade removed the child row when its warehouse was deleted. Giving constraints a name= makes these messages and later migrations far easier to read.

Choosing sensible types

  • Store money as Numeric(12, 2) or as an integer count of cents, never as Float.
  • Store timestamps in UTC and use DateTime(timezone=True) on databases that support it.
  • Prefer Text over a guessed String(255) when length does not matter to your rules.
  • Use Enum or a CHECK constraint for fixed value sets so typos cannot enter.
  • Add an index to columns you filter or join on often; chapter 8 returns to this.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which default is part of the table definition in the database itself?

    server_default is written into CREATE TABLE, so it applies to every insert, including raw SQL.
  2. 2. Which type is best for storing prices that must be exact?

    Numeric stores exact decimals (Decimal objects). Float introduces rounding errors.
  3. 3. What exception does SQLAlchemy raise when a CHECK or UNIQUE constraint fails?

    Constraint violations surface as IntegrityError, wrapping the driver's original error.
  4. 4. What does ForeignKey("warehouse.id", ondelete="CASCADE") do?

    ON DELETE CASCADE makes the database delete referencing rows along with the parent.

Summary

  • Generic types convert between Python and database values; choose them deliberately (Numeric for money, DateTime for timestamps).
  • default and onupdate run in Python; server_default is stored in the database.
  • Constraints (nullable, unique, CheckConstraint, ForeignKey, Index) make the database enforce your rules, and violations raise IntegrityError.
  • Name your constraints.

Next: INSERT, UPDATE and DELETE in Core