no control of DDL sequences for indexes, FK constraints that are mutually dependent

the whole system described at http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html#controlling-ddl-sequences only takes place for constraints that are rendered inline within the table definition via the _create_rule() callable checked in compiler.py. This callable is not consulted anywhere in ddl.py when it goes to create indexes, foreign keys, or for that matter sequences.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    favorite_b = Column(Integer)


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(Integer)

from sqlalchemy.schema import CreateIndex, AddConstraint

idx = Index('aidx', A.x)

fk1 = ForeignKeyConstraint([B.a_id], [A.id], name='afk')
fk2 = ForeignKeyConstraint([A.favorite_b], [B.id], name='bfk')

from sqlalchemy import event

event.listen(
    Base.metadata,
    'after_create',
    CreateIndex(idx).execute_if(dialect='postgresql')
)

event.listen(
    Base.metadata,
    'after_create',
    AddConstraint(fk1).execute_if(dialect='postgresql')
)
event.listen(
    Base.metadata,
    'after_create',
    AddConstraint(fk2).execute_if(dialect='postgresql')
)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

Comments (6)