Issue #3067 resolved

Naming convention exception for Boolean type on PostgreSQL

Maik Riechert
created an issue
mybool = Column(Boolean, nullable=False, default=False)

I use PostgreSQL which has a native boolean type. If I then also use naming convention:

"ck": "ck_%(table_name)s_%(constraint_name)s",

Then sqlalchemy complains:

sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.

If I define the column as

isPublic = Column(Boolean(create_constraint=False), nullable=False, default=False)

it works, so I think this is a bug in which sqlalchemy doesn't connect the fact that there is a native Boolean and no constraint exists actually.

Comments (26)

  1. Michael Bayer repo owner
    • attached 3067.patch
    • edited description
    • changed component to sql
    • changed milestone to 1.0

    naming is obviously taking awhile to get off the ground. Stick with the workaround for now, this is sort of a behavioral enhancement IMHO, the attached patch defers out the naming until compile time. But I'm not sure if this throws off existing use cases, 1.0 for now...

  2. Michael Bayer repo owner

    all databases have entirely different variants of DDL so --sql mode requires that a dialect be present just like in any other case, no problem there.

    not sure if I like this approach still as this means special steps need to be taken in order to read constraint.name. still haven't devised a solution to this which covers every base.

  3. Michael Bayer repo owner
    • Fix bug in naming convention feature where using a check constraint convention that includes constraint_name would then force all :class:.Boolean and :class:.Enum types to require names as well, as these implicitly create a constraint, even if the ultimate target backend were one that does not require generation of the constraint such as Postgresql. The mechanics of naming conventions for these particular constraints has been reorganized such that the naming determination is done at DDL compile time, rather than at constraint/table construction time. fixes #3067

    → <<cset d2193f53c10d>>

  4. Michael Bayer repo owner
    • Fix bug in naming convention feature where using a check constraint convention that includes constraint_name would then force all :class:.Boolean and :class:.Enum types to require names as well, as these implicitly create a constraint, even if the ultimate target backend were one that does not require generation of the constraint such as Postgresql. The mechanics of naming conventions for these particular constraints has been reorganized such that the naming determination is done at DDL compile time, rather than at constraint/table construction time. fixes #3067

    → <<cset d462dbde9976>>

  5. Rob van der Linde

    I noticed that SQLite runs into the exact same issue with a boolean type and a naming convention as described in the Alembic docs.

    Throws the same error:

    sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.

    Should this be opened as a new bug or not? I am using SQLAlchemy 1.0.13 on Python 3.4

  6. Michael Bayer repo owner

    Rob van der Linde you actually have the opposite error; you're on SQLite which actually requires the constraint and thus the naming convention. The issue here is about errors being raised in the case when no such naming convention is required (e.g. boolean on Postgresql).

  7. George Xie

    encountered this error on sqlalchemy 1.0.14 (with flask-sqlalchemy 2.1)

    naming conventions code is copied from document http://flask-sqlalchemy.pocoo.org/2.1/config/#using-custom-metadata-and-naming-conventions and http://docs.sqlalchemy.org/en/rel_1_0/core/constraints.html#constraint-naming-conventions

    with model:

    class Staff(db.Model):
        name = db.Column(db.Unicode(16), nullable=False)
        email = db.Column(db.String(64), unique=True, nullable=False)
    

    run create_all(), got:

    sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
    

    full stack: https://pastebin.mozilla.org/8890960

  8. Roy Hyunjin Han

    Rob van der Linde Michael Bayer I can confirm that sqlalchemy 1.0.15 with SQLite works fine with a naming convention if there are no Boolean fields, but raises an exception as soon as we reintroduce the Boolean fields. A quick workaround is to use Integer instead of Boolean, if we want to stick to using a naming convention.

    sqlalchemy.exc.InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
    
  9. Brice Maron

    Hey, i got this issue with a Mysql DB too. the odd thing is that mysql does not support check constraints. so i don't really see the point of naming Boolean types or changing the naming convention in Mysql... it should silently pass as in pg , no ?

    btw the doc is somewhat misleading here :

    This convention will only be consulted for the CHECK constraint if we run against a database without a native BOOLEAN type like SQLite or MySQL.

  10. Michael Bayer repo owner

    Brice Maron "this issue " I assume you mean you are using a CK constraint naming convention that looks for the name of the constraint as part of the name. so yeah, MySQL ignores the constraints, why use a naming convention for them ? it doesn't "pass" because SQLA goes ahead and generates CHECK on MySQL, there's no logic to omit these as they are silently accepted by MySQL (and one should really ask, why is that).

    btw the doc is somewhat misleading here

    you need to be more specific

  11. Brice Maron

    hi, thanks for your reply :)

    yes i got the naming convention exception while trying to use a boolean.

    I totaly agree with you, the comportement on mysql is wierd and mysql should either fail or accept the condition and do something. I also understant that sqlalchemy generate the check even if the database just "ignore it".

    but having to name something that does not exists seems a bit weird.

  12. Log in to comment