Sqlalchemy Not Producing Proper Sql Statement For Multi Column Uniqueconstraints

Below are the two different attempts I have made in trying to achieve a multi-column unique constraint in sqlalchemy, both of which seems to have failed since a proper SQL statemen

Solution 1:

When using a UniqueConstraint in a declarative table configuration, you need to specify it using the __table_args__ attribute (note the underscores on both sides of the name:

class Bar(Base):
    __tablename__ = "bar"
    __table_args__ = (UniqueConstraint("baz", "qux"),)

    id =Column(Integer, primary_key=True)
    baz =Column(Integer, ForeignKey("foo.id"))
    qux =Column(Integer, ForeignKey("foo.id"))

class Cruft(Base):
    __tablename__ = "cruft"
    __table_args__ = (UniqueConstraint("bar", "qux"),)

    id =Column(Integer, primary_key=True)
    bar =Column(Integer, ForeignKey("foo.id"))
    qux =Column(Integer, ForeignKey("foo.id"))

The attribute must be either a tuple or a dictionary.

Creating these two tables now results in:

2013-05-0913:38:44,180 INFO sqlalchemy.engine.base.Engine 
CREATETABLE cruft (
    id INTEGERNOTNULL, 
    bar INTEGER, 
    qux INTEGER, 
    PRIMARY KEY (id), 
    UNIQUE (bar, qux), 
    FOREIGN KEY(bar) REFERENCES foo (id), 
    FOREIGN KEY(qux) REFERENCES foo (id)
)

...

2013-05-0913:38:44,181 INFO sqlalchemy.engine.base.Engine 
CREATETABLE bar (
    id INTEGERNOTNULL, 
    baz INTEGER, 
    qux INTEGER, 
    PRIMARY KEY (id), 
    UNIQUE (baz, qux), 
    FOREIGN KEY(baz) REFERENCES foo (id), 
    FOREIGN KEY(qux) REFERENCES foo (id)
)

Also see the Setting up Constraints when using the Declarative ORM Extension section of the Defining Constraints and Indexes chapter.