Modifiers, keys, and indexes

Types define value storage; modifiers describe nullability, generation, defaults, and constraints. These are shared by Blueprint, Column, and Model attributes. This guide covers every ColumnDefinition modifier and every blueprint constraint operation.

Column modifiers

ColumnDefinition(column_type: ColumnType, options: ColumnOptions | None = None) initializes name="" with no key, index, uniqueness, autoincrement, comment, or default. nullable also starts False. Every modifier returns the same instance of ColumnDefinition for chaining; hasDefault returns bool. Annotations do not create a copy.

primary

primary() -> ColumnDefinition

Mark a primary key column. is_primary initially equals False. SQL translation enforces NOT NULL even when nullable() was also called. This does not generate values: combine autoIncrement for generated integer keys, or use id().

from orionis.orm import Integer, String


value = Integer().primary()

nullable

nullable() -> ColumnDefinition

Allow SQL NULL, initially disallowed. This accepts no boolean and cannot switch back to NOT NULL using nullable(False). JSON distinguishes SQL NULL from JSON null with none_as_null.

from orionis.orm import Integer, String


value = String(120).nullable()

default

default(value: Any) -> ColumnDefinition

Register a value or a zero argument callable. Static non-None values also become DDL server_default values; callables execute for typed inserts and create no SQL DEFAULT. default(None) counts as a declared default but emits no explicit DEFAULT NULL. A lambda returning CURRENT_TIMESTAMP is a string, not a SQL expression.

from orionis.orm import Integer, String


value = String(36).default(lambda: "pending")

unique

unique() -> ColumnDefinition

Mark a single column UNIQUE constraint. No name or additional columns are accepted; use table.unique for named or composite uniqueness. Actual enforcement depends on the backend: Redshift does not enforce uniqueness.

from orionis.orm import Integer, String


value = String(160).unique()

index

index() -> ColumnDefinition

Request a single column index, initially disabled. Combining unique() may represent uniqueness as a unique index through the toolkit. Physical support is backend dependent; Redshift does not provide traditional secondary indexes.

from orionis.orm import Integer, String


value = String(80).index()

foreign

foreign(reference: str) -> ColumnDefinition

Accept exactly table.column with two nonempty segments; other formats raise ValueError. No cascade actions, name, or update options are accepted. For a database schema, use a qualified ref_table on CompositeForeignKey. This configures neither an ORM relationship nor a Python check for referenced data.

from orionis.orm import Integer, String


value = Integer().foreign("users.id")

autoIncrement

autoIncrement() -> ColumnDefinition

Request automatic generation on an appropriate column, normally an integer primary key. This does not validate backend support for every combination. Oracle receives Identity; SQL Server disables implicit IDENTITY for integer keys without this marker. SQLite changes generated primary BIGINT into INTEGER. Redshift uses identity=(1,1) but does not return the generated identifier to the model.

from orionis.orm import Integer, String


value = Integer().primary().autoIncrement()

comment

comment(text: str) -> ColumnDefinition

Convert text to a string, trim its ends, and retain the comment. Persistence depends on the backend: SQLite does not store column comments through generated DDL. This changes no record data.

from orionis.orm import Integer, String


value = String(120).comment("Public display name")

hasDefault

hasDefault() -> bool

Return bool without modifying the definition. Distinguishes no default call from default(None).

from orionis.orm import Integer, String


value = String(120).default(None).hasDefault()

Table constraints and timestamps

Blueprint.timestamps

timestamps(*, timezone: bool=False) -> None

Add nullable created_at and updated_at using DateTime(timezone=False). timezone is keyword only and defaults to False. Returns None. This schedules no automatic updates and adds no deleted_at.

from orionis.database.schema.blueprint import Blueprint


table = Blueprint()
table.timestamps(timezone=True)

Blueprint.primaryKey

primaryKey(*columns: str) -> PrimaryKey

Collect primary key names and return PrimaryKey. Two or more columns generate the composite constraint. Current limit: a single column only sets TableDefinition.primary_key and does not activate its is_primary flag; use table.integer(“code”).primary() or table.id() to generate a single column PRIMARY KEY. Supply at least one real column. Do not combine this with id() or primary() columns: Schema rejects duplicate primary definitions with ValueError. A schema composite primary key does not imply general composite key support in Model.

from orionis.database.schema.blueprint import Blueprint


table = Blueprint()
table.primaryKey("team_id", "user_id")

Blueprint.unique

unique(*columns: str, name: str | None=None) -> Unique

Define UNIQUE over one or more columns and return Unique. name=None leaves naming to the backend. Column order is preserved. Redshift treats the constraint as informational and does not guarantee concurrent exclusion.

from orionis.database.schema.blueprint import Blueprint


table = Blueprint()
table.unique("team_id", "email", name="uq_team_email")

Blueprint.index

index(*columns: str, name: str | None=None, unique: bool=False) -> Index

Define a single or composite index and return Index. unique defaults to False; name=None produces ix_<logical_table>_<columns>. Automatic naming uses the unprefixed logical table. Partial indexes, expression indexes, index methods, and dialect options are not exposed.

from orionis.database.schema.blueprint import Blueprint


table = Blueprint()
table.index("team_id", "active", name="ix_team_active")

Blueprint.foreignKey

foreignKey(column: str, ref_table: str, ref_column: str, name: str | None=None) -> ForeignKey

Declare a single column reference and return ForeignKey, carrying .foreign: CompositeForeignKey. Column arguments are positional; optional name may also be supplied by keyword. ref_table accepts schema.table. Use TableDefinition.foreign_keys for multiple columns. No fluent onDelete/onUpdate/cascade is available.

from orionis.database.schema.blueprint import Blueprint


table = Blueprint()
table.foreignKey("user_id", "users", "id", name="fk_membership_user")

Blueprint.comment

comment(text: str) -> Comment

Attach a table comment and return Comment. Unicode NFC is normalized, ASCII control characters become spaces, repeated whitespace collapses, and ends are trimmed. The last declaration wins if repeated. Storage depends on the backend.

from orionis.database.schema.blueprint import Blueprint


table = Blueprint()
table.comment("Team memberships")

Table with keys and indexes

Create users and teams with their referenced keys first. This example uses a physical composite key on memberships and does not assume that a model treats both columns as primary_key.

from orionis.database import Migration
from orionis.support.facades import Schema


class CreateMemberships(Migration):
    async def up(self) -> None:
        async with Schema.create("memberships") as table:
            table.integer("team_id")
            table.integer("user_id")
            table.string("email", 160)
            table.boolean("active").default(True)
            table.primaryKey("team_id", "user_id")
            table.unique("team_id", "email", name="uq_membership_email")
            table.index("team_id", "active", name="ix_membership_active")
            table.foreignKey("user_id", "users", "id")
            table.comment("Team memberships")

    async def down(self) -> None:
        await Schema.drop("memberships")

The declared foreign key references only users.id. Add another foreignKey toward teams.id to enforce team_id too. Enforcement depends on backend/configuration capabilities; an ORM relationship by itself adds no constraint. Unknown columns, incompatible keys, duplicates, and DDL syntax failures propagate during compilation or execution. The blueprint does not validate every combination in advance.

Equivalent definition objects

The public wrappers retain the same data used by Blueprint. Their constructors are synchronous and execute no SQL:

Comment(text: str) -> None

ForeignKey(column: str, ref_table: str, ref_column: str, name: str | None=None) -> None

Index(*columns: str, name: str | None=None, unique: bool=False) -> None

PrimaryKey(*columns: str) -> None

Unique(*columns: str, name: str | None=None) -> None

Timestamps(*, timezone: bool=True) -> None

Comment.text retains the normalized comment; PrimaryKey.columns is the column tuple; ForeignKey.foreign retains CompositeForeignKey; Index.constraint retains TableIndex; Unique.constraint retains UniqueConstraint. Timestamps(timezone=True) has a different default from Blueprint.timestamps(timezone=False): the marker retains timezone but is not a column, and the public usage route is table.timestamps(). Do not pass it as a column factory.

Structured constraints, ColumnOptions properties, and composite foreign keys are covered in ORM definitions. See alterations to rename, remove, or change existing constraints.