constraint name unexpected when using metadata.naming_conventions

Issue #2991 resolved
Marek Baczyński created an issue

DB: SQL Server 2008R2

SQLAlchemy==0.9.3

from sqlalchemy import Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import Enum, Text, Sequence, BigInteger, DateTime, Boolean
from sqlalchemy import UniqueConstraint, CheckConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)
Base = declarative_base(metadata=metadata)


class Table(Base):
    __tablename__ = 'table'
    id = Column(Integer, primary_key=True)
    col1 = Column(Enum('one', 'two', name='col1'), nullable=True)
    col2 = Column(Boolean(name='col2'), nullable=True)

metadata.create_all() results in this sql:

INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE [table] (
        id INTEGER NOT NULL IDENTITY(1,1),
        col1 VARCHAR(3) NULL,
        col2 BIT NULL,
        CONSTRAINT pk_table PRIMARY KEY (id),
 -- expected ck_table_col1
        CONSTRAINT ck_table_ck_table_col1 CHECK (col1 IN ('one', 'two')),
 -- expected ck_table_col2
        CONSTRAINT ck_table_ck_table_col2 CHECK (col2 IN (0, 1))
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT

Comments (5)

  1. Mike Bayer repo owner

    well here's patch num. 1 on that

    diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
    index fee424e..84e7e43 100644
    --- a/lib/sqlalchemy/sql/sqltypes.py
    +++ b/lib/sqlalchemy/sql/sqltypes.py
    @@ -1131,7 +1131,7 @@ class Enum(String, SchemaType):
                             _create_rule=util.portable_instancemethod(
                                             self._should_create_constraint)
                         )
    -        table.append_constraint(e)
    +        assert e.table is table
    
         def adapt(self, impltype, **kw):
             schema = kw.pop('schema', self.schema)
    
  2. Mike Bayer repo owner

    patch num. 2:

    diff --git a/lib/sqlalchemy/sql/naming.py b/lib/sqlalchemy/sql/naming.py
    index ee99ccb..bbb8431 100644
    --- a/lib/sqlalchemy/sql/naming.py
    +++ b/lib/sqlalchemy/sql/naming.py
    @@ -22,7 +22,7 @@ class ConventionDict(object):
             self._is_fk = isinstance(const, ForeignKeyConstraint)
             self.table = table
             self.convention = convention
    -        self._const_name = const.name
    +        self._const_name = const._orig_name = getattr(const, '_orig_name', const.name)
    
         def _key_table_name(self):
             return self.table.name
    
  3. Mike Bayer repo owner
    • Fixed bug in new :paramref:.MetaData.naming_convention feature where the name of a check constraint making use of the "%(constraint_name)s" token would get doubled up for the constraint generated by a boolean or enum type, and overall duplicate events would cause the "%(constraint_name)s" token to keep compounding itself. fixes #2991

    → <<cset d85d6f9a3f1d>>

  4. Mike Bayer repo owner
    • Added a new feature :func:.schema.conv, the purpose of which is to mark a constraint name as already having had a naming convention applied. This token will be used by Alembic migrations as of Alembic 0.6.4 in order to render constraints in migration scripts with names marked as already having been subject to a naming convention. re: #2991

    → <<cset 36792434c74d>>

  5. Log in to comment