Naming convention exception for Boolean type on PostgreSQL

[Maik Riechert]

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.

[Mike Bayer]

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...

[Maik Riechert]

It will probably fail when using "alembic --sql" as there's no connection then. How should it know which SQL backend (mysql, postgresql,..) is used?

[Mike Bayer]

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.

[Mike Bayer]

updated patch. name is deferred just for those elements

[Mike Bayer]

this also fixes #3068

[Mike Bayer]

updated

[Mike Bayer]

Issue #3068 was marked as a duplicate of this issue.

[Mike Bayer]

• 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>>

[Mike Bayer]

• 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>>

[Mike Bayer]

regression. See #3144

[Mike Bayer]

kind of a regression in #3299 as well.

[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

[Mike Bayer]

@robvdl 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).

[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

[Mike Bayer]

@georgexsh there's no CHECK contraint in that model. that's not the code for that stack trace.

[Roy Hyunjin Han]

@robvdl @zzzeek 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.

[Mike Bayer]

@invisibleroads you need to give a name to your Boolean field or tell it to not generate a constraint. http://docs.sqlalchemy.org/en/latest/core/constraints.html?highlight=boolean#configuring-naming-for-boolean-enum-and-other-schema-types

[Roy Hyunjin Han]

We're confirming that Boolean(name='xyz') works. Thank you so much for making sqlalchemy, dogpile.cache, alembic.

[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.

[Mike Bayer]

@emerzh "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

[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.

[Mike Bayer]

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

absolutely. so again, why does your naming convention have a "ck" entry in it?