AddConstraint example fails with binding error

[Wichert Akkerman]

In order to support mark a constraint as deferrable with dialects that support it I use this code:

def deferrable_supported(ddl, target, bind, **kw):
    """Check if deferrable constraints are supported.

    This function can be used as a callable for
    :ref:`execute_if <sqlalchemy:sqlalchemy.schema.DDLElement.execute_if>` to
    only run DDL statements on databases that support deferrable constraints.
    """
    return bind.dialect.name == 'postgresql'


def deferrable_not_supported(ddl, target, bind, **kw):
    """Check if deferrable constraints are not supported.

    This function can be used as a callable for
    :ref:`execute_if <sqlalchemy:sqlalchemy.schema.DDLElement.execute_if>` to
    only run DDL statements on databases that do not support deferrable
    constraints.
    """
    return bind.dialect.name not in ('postgresql', 'sqlite')


for constraint in _generic_sku_constraints:
    listen(SKU.__table__, 'after_create',
            schema.AddConstraint(constraint)
                .execute_if(callable_=deferrable_not_supported))

for constraint in _deferrable_sku_constraints:
    listen(SKU.__table__, 'after_create',
            schema.AddConstraint(constraint)
                .execute_if(callable_=deferrable_supported))

This code is modeled on the example in the DDL chapter. This code fails with SQLAlchemy 0.9 with a InvalidRequestError: This constraint is not bound to a table. Did you mean to call table.append_constraint(constraint) ? error.

[Mike Bayer]

you need to call append_constraint if the constraint itself doesn't have anything in it that identifies it as part of a table. Can you add more detail please if that's not the issue?

[Wichert Akkerman]

That seems to be part of the issue. Testing this further there are two problems: 1) you need to use table.append_constraint, and 2) you must do this in an before_create event. A working version of my code looks like this:

@listens_for(SKU.__table__, 'before_create')
def add_sku_constraints(cls, connection, **kw):
    if deferrable_supported(connection):
        constraints = _deferrable_sku_constraints
    else:
        constraints = _generic_sku_constraints
    for constraint in constraints:
        cls.append_constraint(constraint)

[Mike Bayer]

"need to use table.append_constraint" is not a bug depending on the constraint we are talking about. I need a full example of this constraint.

Also the docs don't assume any kind of "auto attachment" at all, the need to call "append_constraint()" is documented:

The CheckConstraint construct can also be constructed externally and associated with the Table afterwards:

constraint = CheckConstraint('length(user_name) >= 8',name="cst_user_name_length")
users.append_constraint(constraint)

[Wichert Akkerman]

The constraints are all UniqueConstraint instances:

_generic_sku_constraints = [
        schema.UniqueConstraint(
            'article_colour_uuid', 'length', 'size_type', 'size',
            name='sku_unique_criteria'),
        schema.UniqueConstraint('retailer_id', 'retailer_uuid',
            name='sku_unique_retailer_uuid'),
        ]


_deferrable_sku_constraints = [
        schema.UniqueConstraint(
            'article_colour_uuid', 'length', 'size_type', 'size',
            name='location_sku_unique_criteria',
            deferrable=True),
        schema.UniqueConstraint('retailer_id', 'retailer_uuid',
            name='location_sku_unique_retailer_uuid',
            deferrable=True),
        ]

[Mike Bayer]

OK, so "ALTER TABLE <x> ADD CONSTRAINT <y>" in SQL needs to specify the table name, and those constraints aren't associated with a table. if you're trying to copy these constraints across many tables you'd need to generate them from a function as they get attached to the table and remain associated with it, using append_constraint. This function should be fine to live within "after_create" though.

[Mike Bayer]

no bug here ...