from sqlalchemy import (
Column,
Computed,
ForeignKey,
Integer,
MetaData,
String,
Table,
)
metadata = MetaData(bind=engine)
singers = Table(
"Singers",
metadata,
Column("SingerId", String(36), primary_key=True, nullable=False),
Column("FirstName", String(200)),
Column("LastName", String(200), nullable=False),
Column("FullName", String(400), Computed("COALESCE(FirstName || ' ', '') || LastName")),
)
albums = Table(
"Albums",
metadata,
Column("AlbumId", String(36), primary_key=True, nullable=False),
Column("Title", String(100), nullable=False),
Column("SingerId", String(36), ForeignKey("Singers.SingerId", name="FK_Albums_Singers"), nullable=False),
)
tracks = Table(
"Tracks",
metadata,
Column("AlbumId", String(36), primary_key=True, nullable=False),
Column("TrackId", Integer, primary_key=True, nullable=False),
Column("Title", String(200), nullable=False),
spanner_interleave_in="Albums",
spanner_interleave_on_delete_cascade=True,
)
metadata.create_all(engine)
This is because SQLAlchemy creates the Singers table first, then attempts to create Tracks before Albums. From the foreign key constraint, it knows that Singers must be created before Albums but it doesn't know that Albums must be created before Tracks.
Since the user specifies the table to interleave in, it would be nice if the dialect automatically calls tracks.add_is_dependent_on(albums).
Is your feature request related to a problem? Please describe.
The following snippet returns
google.api_core.exceptions.NotFound: 404 Table not found: Albums:This is because SQLAlchemy creates the Singers table first, then attempts to create Tracks before Albums. From the foreign key constraint, it knows that Singers must be created before Albums but it doesn't know that Albums must be created before Tracks.
Describe the solution you'd like
Since the user specifies the table to interleave in, it would be nice if the dialect automatically calls
tracks.add_is_dependent_on(albums).Describe alternatives you've considered
Users can manually put in a dependency by calling
tracks.add_is_dependent_on(albums). But they may not necessarily understand why their DDL statements are failing so may not know to call it or call it in the right place.