I apologize in advance if I am incorrect about this, but I've been researching for the past hour and I think I've found a bug in the way that SQLAlchemy tries to get foreign keys from a SQLite database. Basically, if a foreign key was defined without an explicit parent key, then the existing implementation will use the child key instead:

However, this is at odds with what SQLite describes in their ForeignKey docs. If I understand them correctly, a null parent key should map automatically to the primary key of the parent table:

The block above uses a shorthand form to create the foreign key constraint. Attaching a "REFERENCES parent-table" clause to a column definition creates a foreign key constraint that maps the column to the primary key of parent-table.

Now, ideally the schema wouldn't be missing the parent key, but that's apparently what SQLAlchemy did when it was generating my SQLite database. I had the following declarative table:

class Assignment(Base):
    owner_id = Column(Integer(), ForeignKey('user.id'))

The DDL for the actual DB is:

CREATE TABLE "assignment"
(
	id INTEGER not null
		primary key,
        ...
	owner_id INTEGER
		references user,
        ...
)

And those are referring to a User table who's primary key is defined as id. Effectively, when it gets my foreign keys using the function linked above, it decides that the parent must be User.owner_id - instead of the correct User.id.

This situation hit me pretty hard when I was trying to run Flask-Migrate (so really Alembic) auto-generate on my models, and it was getting very confused about how to read my existing models with regards to the sqlite database. I'm not sure of more specifics on all this stuff, and I hope I can probably avoid using auto-generate for now, but this is something of a problem for me long term.