[Bug]: Issue with foreign keys on explicitly indexed column

[jobh]

What happened?

This pair of table definitions fails:

Base = declarative_base()

class FakeModel(Base):  # type: ignore
    __tablename__ = "fake"

    id = Column(Integer, Sequence("fakemodel_id_sequence"), index=True, primary_key=True)

class DerivedModel(Base):  # type: ignore
    __tablename__ = "derived"

    id = Column(Integer, Sequence("derivedmodel_id_sequence"), primary_key=True)
    fake = Column(Integer, ForeignKey("fake.id"))

eng = create_engine("duckdb:///:memory:")
Base.metadata.create_all(eng)

↓

sqlalchemy.exc.DBAPIError: (duckdb.duckdb.Error) Cannot alter entry "fake" because there are entries that depend on it.
[SQL: 
CREATE TABLE derived (
        id INTEGER NOT NULL, 
        fake INTEGER, 
        PRIMARY KEY (id), 
        FOREIGN KEY(fake) REFERENCES fake (id)
)

If I remove index=True on FakeModel.id, it works fine. The index is probably automatic on PK columns, but it shouldn't be an error to make it explicit.

DuckDB Engine Version

0.11.2

DuckDB Version

0.10.1

SQLAlchemy Version

2.0.29

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[JianpingCAI]

I have encountered the same error (same versions), for the association table creation,

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    ForeignKey,
    Table,
    Sequence,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

engine = create_engine("duckdb:///:memory:", echo=True)
Base = declarative_base()

# Junction table for TestSuite and TestCase many-to-many relationship
suite_case_association = Table(
    "suite_case_association",
    Base.metadata,
    Column("test_suite_id", Integer, ForeignKey("test_suites.id"), primary_key=True),
    Column("test_case_id", Integer, ForeignKey("test_cases.id"), primary_key=True),
)

# TestSuite model
id_seq1 = Sequence("id_seq1")
class TestSuite(Base):
    __tablename__ = "test_suites"
    # id = Column(Integer, primary_key=True)
    id = Column(
        Integer,
        id_seq1,
        server_default=id_seq1.next_value(),
        primary_key=True,
    )
    name = Column(String, index=True)
    description = Column(String)
    test_cases = relationship(
        "TestCase", secondary=suite_case_association, back_populates="test_suites"
    )

# TestCase model
id_seq2 = Sequence("id_seq2")
class TestCase(Base):
    __tablename__ = "test_cases"
    # id = Column(Integer, primary_key=True)
    id = Column(
        Integer,
        id_seq2,
        server_default=id_seq2.next_value(),
        primary_key=True,
    )
    name = Column(String, index=True)
    description = Column(String)
    expected_outcome = Column(String)
    test_suites = relationship(
        "TestSuite", secondary=suite_case_association, back_populates="test_cases"
    )

Base.metadata.create_all(engine)

Error message,

File "../python3.8/site-packages/duckdb_engine/__init__.py", line 162, in execute
    self.__c.execute(statement, parameters)
sqlalchemy.exc.DBAPIError: (duckdb.duckdb.Error) Cannot alter entry "test_suites" because there are entries that depend on it.
[SQL: 
CREATE TABLE suite_case_association (
        test_suite_id INTEGER NOT NULL, 
        test_case_id INTEGER NOT NULL, 
        PRIMARY KEY (test_suite_id, test_case_id), 
        FOREIGN KEY(test_suite_id) REFERENCES test_suites (id), 
        FOREIGN KEY(test_case_id) REFERENCES test_cases (id)
)

]

The root cause is the two index=True of the name fields.