AddConstraint example fails with binding error
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.
Comments (7)
-
repo owner -
repo owner -
reporter 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)
-
repo owner "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)
-
reporter 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), ]
-
repo owner 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.
-
repo owner - changed status to closed
no bug here ...
- Log in to comment
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?