sqlalchemy 2.0.51: ForeignKey

# sqlalchemy ForeignKey API

This sample pins `sqlalchemy` 2.0.51 and verifies `sqlalchemy.ForeignKey` for schema definitions and ORM relationship mappings.

The contract verifies:
- `ForeignKey` in Core `Table` definitions attaches to columns, resolves target column expressions, and preserves constraint options (`ondelete`, `name`).
- `ForeignKey` initialized with a direct `Column` reference correctly resolves target column and fullname.
- Declarative ORM mapping with `mapped_column(ForeignKey(...))` enables bidirectional relationship navigation and foreign key persistence via `Session`.
- Accessing an unresolvable or missing foreign key column target raises `NoReferencedTableError`.

Clean-room public code sample — generation instructions

Write a brand-new, minimal, self-contained code sample in this clean-room directory.
Do not copy, paraphrase, or reference any existing project source. Work only from this spec.

A csx.json manifest scaffold already exists. Do not recreate it from memory. Preserve its case.goal, packages and symbols; fill its empty case.contract with exact assertions and correct its environment, commands and verifierAdapter for the files you generate.

Goal: verify sqlalchemy.ForeignKey in pkg:pypi/sqlalchemy@2.0.51
Kind: HOW

Use EXACTLY these public packages and versions:
  - pkg:pypi/sqlalchemy@2.0.51
Demonstrate these symbols/APIs:
  - sqlalchemy.ForeignKey

Rules:
  - One focused purpose; the smallest project that proves the goal.
  - Include a contract test (test/contract.*) that runs OFFLINE and exits 0 exactly when the goal behavior works.
  - Pin every dependency with a lockfile so resolution is reproducible.
  - No secrets, credentials, or tokens. No real URLs (only example.com or localhost). No absolute paths.
  - No personal names, emails, company names, or project identifiers of any kind.
  - No binaries and no generated output (node_modules, dist, target, venv, .git, .env).
  - Keep it under 200 files and 256KB packed.

{"case":{"caseId":"case:sha256:70f43e5ed62a9309442c7c40e4943ff70ec520110549132f686ed18161478468","contract":["ForeignKey in Core Table definitions links target columns and preserves options such as ondelete and constraint name","ForeignKey initialized directly with a Column object correctly resolves target column and target fullname","Declarative ORM models using mapped_column(ForeignKey(...)) maintain bidirectional relationships and referential persistence","Accessing unresolved or dangling ForeignKey column target raises NoReferencedTableError"],"goal":"verify sqlalchemy.ForeignKey in pkg:pypi/sqlalchemy@2.0.51","kind":"HOW","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"symbols":["sqlalchemy.ForeignKey"]},"contractCommand":["python","test/contract.py"],"environment":{"arch":"x64","distro":"ubuntu","ecosystem":"pypi","language":"python","libc":"glibc","libcVersion":"2.39","os":"linux","osVersionBucket":"24","packageManager":"pip","runtime":"python","schemaVersion":1},"license":"MIT-0","packages":["pkg:pypi/sqlalchemy@2.0.51"],"schemaVersion":1,"subject":"pkg:pypi/sqlalchemy@2.0.51","symbols":["sqlalchemy.ForeignKey"],"verifierAdapter":"python@1"}

greenlet==3.5.5
sqlalchemy==2.0.51
typing-extensions==4.16.0

"""SQLAlchemy 2.0 ForeignKey schema and ORM demonstration."""

from typing import List
from sqlalchemy import (
    Column,
    ForeignKey,
    Integer,
    MetaData,
    String,
    Table,
    create_engine,
    Engine,
)
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
)


class Base(DeclarativeBase):
    pass


class Department(Base):
    __tablename__ = "departments"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(50), nullable=False)
    employees: Mapped[List["Employee"]] = relationship(
        back_populates="department",
        cascade="all, delete-orphan",
    )

    def __repr__(self) -> str:
        return f"<Department(id={self.id}, name='{self.name}')>"


class Employee(Base):
    __tablename__ = "employees"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(50), nullable=False)
    department_id: Mapped[int] = mapped_column(
        ForeignKey("departments.id", ondelete="CASCADE", name="fk_employee_dept")
    )
    department: Mapped["Department"] = relationship(back_populates="employees")

    def __repr__(self) -> str:
        return f"<Employee(id={self.id}, name='{self.name}', department_id={self.department_id})>"


def create_core_schema() -> tuple[MetaData, Table, Table]:
    """Create Core tables with ForeignKey definitions."""
    metadata = MetaData()
    parents = Table(
        "parents",
        metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String(50), nullable=False),
    )
    children = Table(
        "children",
        metadata,
        Column("id", Integer, primary_key=True),
        Column(
            "parent_id",
            Integer,
            ForeignKey("parents.id", ondelete="CASCADE", name="fk_children_parents"),
            nullable=False,
        ),
    )
    return metadata, parents, children


def create_database(db_url: str = "sqlite:///:memory:") -> Engine:
    """Create in-memory SQLite database and initialize ORM tables."""
    engine = create_engine(db_url, echo=False)
    Base.metadata.create_all(engine)
    return engine

{
  "schemaVersion": 1,
  "goal": "verify sqlalchemy.ForeignKey in pkg:pypi/sqlalchemy@2.0.51",
  "kind": "HOW",
  "packages": [
    "pkg:pypi/sqlalchemy@2.0.51"
  ],
  "symbols": [
    "sqlalchemy.ForeignKey"
  ]
}

"""Contract verification test for sqlalchemy.ForeignKey in SQLAlchemy 2.0."""

import os
import sys

# Add sample root directory to sys.path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import Column, ForeignKey, Integer, MetaData, Table, select
from sqlalchemy.orm import Session
from sqlalchemy.exc import NoReferencedTableError
from sample import (
    Department,
    Employee,
    create_core_schema,
    create_database,
)


def test_foreign_key_core_metadata() -> None:
    """Assertion 1: ForeignKey in Core Table definitions links target columns and preserves options such as ondelete and constraint name."""
    metadata, parents, children = create_core_schema()
    parent_id_col = children.c.parent_id

    assert len(parent_id_col.foreign_keys) == 1, "Column must have exactly one foreign key"
    fk = next(iter(parent_id_col.foreign_keys))

    assert isinstance(fk, ForeignKey), f"Expected ForeignKey instance, got {type(fk)}"
    assert fk.target_fullname == "parents.id", f"Expected 'parents.id', got {fk.target_fullname}"
    assert fk.ondelete == "CASCADE", f"Expected 'CASCADE', got {fk.ondelete}"
    assert fk.name == "fk_children_parents", f"Expected 'fk_children_parents', got {fk.name}"
    assert fk.column is parents.c.id, "ForeignKey column target must resolve to parent column"


def test_foreign_key_column_object_initialization() -> None:
    """Assertion 2: ForeignKey initialized directly with a Column object correctly resolves target column and target fullname."""
    meta = MetaData()
    parent_tbl = Table("p", meta, Column("id", Integer, primary_key=True))
    col_fk = ForeignKey(parent_tbl.c.id)

    assert col_fk.column is parent_tbl.c.id, "ForeignKey column must reference parent table column"
    assert col_fk.target_fullname == "p.id", "Target fullname must be 'p.id'"


def test_foreign_key_orm_mapping_and_relationship() -> None:
    """Assertion 3: Declarative ORM models using mapped_column(ForeignKey(...)) maintain bidirectional relationships and referential persistence."""
    engine = create_database()

    # Verify column ForeignKey constraint in ORM table metadata
    emp_table = Employee.__table__
    dept_id_col = emp_table.columns["department_id"]
    assert len(dept_id_col.foreign_keys) == 1
    fk = next(iter(dept_id_col.foreign_keys))
    assert fk.target_fullname == "departments.id"
    assert fk.ondelete == "CASCADE"
    assert fk.name == "fk_employee_dept"

    with Session(engine) as session:
        dept = Department(name="Engineering")
        emp1 = Employee(name="Alice", department=dept)
        emp2 = Employee(name="Bob", department=dept)
        session.add(dept)
        session.commit()

        assert dept.id is not None
        assert emp1.id is not None
        assert emp2.id is not None
        assert emp1.department_id == dept.id
        assert emp2.department_id == dept.id

        # Query child entity and navigate to parent relationship
        stmt = select(Employee).where(Employee.name == "Alice")
        loaded_emp = session.scalars(stmt).one()
        assert loaded_emp.department.name == "Engineering"
        assert len(loaded_emp.department.employees) == 2


def test_foreign_key_unresolved_reference_raises_error() -> None:
    """Assertion 4: Accessing unresolved or dangling ForeignKey column target raises NoReferencedTableError."""
    bad_metadata = MetaData()
    bad_table = Table(
        "bad_table",
        bad_metadata,
        Column("id", Integer, primary_key=True),
        Column("invalid_ref", Integer, ForeignKey("nonexistent_parent_table.id")),
    )
    bad_fk = next(iter(bad_table.c.invalid_ref.foreign_keys))

    try:
        _ = bad_fk.column
        raise AssertionError("Expected NoReferencedTableError when accessing unresolved foreign key column")
    except NoReferencedTableError:
        pass


def main() -> None:
    test_foreign_key_core_metadata()
    test_foreign_key_column_object_initialization()
    test_foreign_key_orm_mapping_and_relationship()
    test_foreign_key_unresolved_reference_raises_error()
    print("All sqlalchemy.ForeignKey contract assertions passed successfully.")


if __name__ == "__main__":
    main()