Constraints and indices
=======================

codegen_database dimensions are defined with ``schema_items`` — a list of
SQLAlchemy ``Column`` objects mixed with codegen_database constraint and
index definitions.  All three use ``{column_name}`` markers to
reference columns, and all three are validated against the actual
table columns at factory time.

.. list-table::
   :header-rows: 1
   :widths: 25 40 35

   * - Class
     - Purpose
     - Import
   * - :class:`~codegen_database.check.CodegenDatabaseCheck`
     - ``CHECK`` constraints
     - ``from codegen_database.check import CodegenDatabaseCheck``
   * - :class:`~codegen_database.index.CodegenDatabaseIndex`
     - Indices (btree, GIN, unique, functional, …)
     - ``from codegen_database.index import CodegenDatabaseIndex``
   * - :class:`~codegen_database.fk.CodegenDatabaseForeignKey`
     - Foreign key constraints (inline, single-column)
     - ``from codegen_database.fk import CodegenDatabaseForeignKey``


Column markers
--------------

Check and index classes reference columns with ``{column_name}``
markers.  At factory time, codegen_database validates that every referenced
column exists on the target table and substitutes the markers with
real column references.

.. code-block:: python

   # Check: the expression is a SQL predicate
   CodegenDatabaseCheck("{price} > 0", name="positive_price")

   # Index: each argument is an expression
   CodegenDatabaseIndex("idx_name", "{name}")
   CodegenDatabaseIndex("idx_lower", "lower({name})")

   # FK: pass inline to Column, reference is "dimension.column"
   Column("customer_id", Integer, CodegenDatabaseForeignKey("customers.id"))

If a marker names a column that does not exist on the table,
codegen_database raises ``CodegenDatabaseValidationError`` at factory time — not
at migration time or at runtime.


Check constraints
-----------------

:class:`~codegen_database.check.CodegenDatabaseCheck` defines a SQL ``CHECK``
constraint.  It takes an expression and a name.

.. code-block:: python

   CodegenDatabaseCheck("{price} > 0", name="positive_price")
   CodegenDatabaseCheck(
       "{end_date} > {start_date}",
       name="valid_date_range",
   )

For simple and append-only dimensions, this becomes a real
``CHECK`` constraint on the table.  For EAV dimensions, it
becomes a trigger function that validates ``NEW.price > 0``
before the main EAV triggers process the row.  The same
``CodegenDatabaseCheck`` definition works on all dimension types —
codegen_database picks the right enforcement strategy automatically.


Indices
-------

:class:`~codegen_database.index.CodegenDatabaseIndex` mirrors the
``sqlalchemy.Index`` constructor: name first, then column
expressions, then keyword arguments passed through to the
underlying index.

.. code-block:: python

   # Simple index
   CodegenDatabaseIndex("idx_products_sku", "{sku}")

   # Unique index
   CodegenDatabaseIndex("uq_products_name", "{name}", unique=True)

   # Functional index with dialect kwargs
   CodegenDatabaseIndex(
       "idx_lower_name", "lower({name})",
       postgresql_using="btree",
   )

   # Multi-column index
   CodegenDatabaseIndex("idx_name_price", "{name}", "{price}")

``CodegenDatabaseIndex`` supports the same keyword arguments as
``sqlalchemy.Index``:

.. list-table::
   :header-rows: 1

   * - Keyword
     - Effect
   * - ``unique=True``
     - Creates a ``UNIQUE`` index
   * - ``postgresql_using="gin"``
     - Uses the GIN index method
   * - ``postgresql_where=text("active")``
     - Partial index (``WHERE active``)
   * - ``postgresql_ops={"data": "jsonb_path_ops"}``
     - Operator class for a column


Foreign keys
------------

:class:`~codegen_database.fk.CodegenDatabaseForeignKey` defines a single-column
foreign key inline on the ``Column`` constructor — analogous to
SQLAlchemy's ``ForeignKey``.

The *reference* string accepts two formats:

.. list-table::
   :header-rows: 1
   :widths: 20 40 40

   * - Format
     - Example
     - When to use
   * - ``"dimension.column"``
     - ``"customers.id"``
     - Target is a codegen_database dimension.  Resolved via the
       dimension registry at factory time.
   * - ``"schema.table.column"``
     - ``"public.orgs.id"``
     - Target is outside codegen_database, or you want full control.
       Passed through to SQLAlchemy as-is.

.. code-block:: python

   # Resolved — codegen_database finds the physical table
   Column(
       "customer_id", Integer,
       CodegenDatabaseForeignKey("customers.id", ondelete="CASCADE"),
   )

   # Raw — passed through directly
   Column(
       "org_id", Integer,
       CodegenDatabaseForeignKey("tenant.orgs.id"),
   )

Cascade options
~~~~~~~~~~~~~~~

Both ``ondelete`` and ``onupdate`` accept any PostgreSQL action:
``CASCADE``, ``SET NULL``, ``SET DEFAULT``, ``RESTRICT``, or
``NO ACTION`` (the default).

.. code-block:: python

   Column(
       "customer_id", Integer,
       CodegenDatabaseForeignKey(
           "customers.id",
           ondelete="CASCADE",
           onupdate="SET NULL",
       ),
   )


Simple dimension example
------------------------

.. literalinclude:: ../scripts/examples/constraints_simple.py
   :language: python
   :start-after: # --- example start ---
   :end-before: # --- example end ---
   :dedent:

.. include:: _generated/dim_constraints_simple.rst


Append-only dimension example
-----------------------------

Constraints and indices on append-only dimensions are placed on
the **attributes table**.  Foreign keys that target an
append-only dimension resolve to the **root table** — the stable
primary key.

.. literalinclude:: ../scripts/examples/constraints_append_only.py
   :language: python
   :start-after: # --- example start ---
   :end-before: # --- example end ---
   :dedent:

.. include:: _generated/dim_constraints_append_only.rst


EAV dimension example
---------------------

EAV dimensions store attributes as rows, not columns.
Table-level ``CHECK`` constraints cannot reference virtual
columns, so codegen_database enforces checks via INSTEAD OF trigger
functions instead.  The same ``CodegenDatabaseCheck`` syntax works — the
enforcement mechanism is chosen automatically.

.. literalinclude:: ../scripts/examples/constraints_eav.py
   :language: python
   :start-after: # --- example start ---
   :end-before: # --- example end ---
   :dedent:

.. include:: _generated/dim_constraints_eav.rst