Issues

Issue #3011 new

naming_convention doesn't support MS SQL DEFAULT constraints

Marek Baczyński
created an issue

SQLAlchemy 0.9.4, Python 3.4.0, MS SQL 2008 R2

This causes issues when dropping columns in alembic.

Table definition:

class SomeTable(Base):
    __tablename__ = 'some_table'

    id = Column(BigInteger, Sequence('some_table_seq_id'), primary_key=True, nullable=False)
    col = Column(String)
    status = Column(Enum('HAS_A', 'HAS_A_OR_B', 'NEITHER', name='status'), server_default='NEITHER', nullable=False)

Causes this SQL to be generated:

CREATE TABLE some_table (
    id BIGINT NOT NULL IDENTITY(1,1), 
    col VARCHAR(max) NULL, 
    status VARCHAR(10) NOT NULL DEFAULT 'NEITHER', 
    CONSTRAINT pk_some_table PRIMARY KEY (id), 
    CONSTRAINT ck_some_table_status CHECK (status IN ('HAS_A', 'HAS_A_OR_B', 'NEITHER'))
)

But what MS SQL does for defaults is it creates a default constraint, which gets a default, unpredictable name:

select [name] from sys.default_constraints
where parent_object_id = object_id('some_table')
and col_name(parent_object_id, parent_column_id) = 'status'

-- name
-- DF__some_tabl__statu__50C6C558

There should be a way of providing a constraint name so the generated SQL looks like this:

CREATE TABLE some_table (
    id BIGINT NOT NULL IDENTITY(1,1), 
    col VARCHAR(max) NULL, 
    status VARCHAR(10) NOT NULL CONSTRAINT [df_some_table_status] DEFAULT 'NEITHER', 
    CONSTRAINT pk_some_table PRIMARY KEY (id), 
    CONSTRAINT ck_some_table_status CHECK (status IN ('HAS_A', 'HAS_A_OR_B', 'NEITHER'))
)

-- check the name

select [name] from sys.default_constraints
where parent_object_id = object_id('some_table')
and col_name(parent_object_id, parent_column_id) = 'status'

-- name
-- df_some_table_status

Comments (6)

  1. Mike Bayer repo owner

    this would be a feature. there are many DDL objects like triggers, stored procedures, etc. for which the naming convention feature has no support. in this case, to my knowledge MSSQL's named "DEFAULT" constraint thing is a vendor-specific behavior.

    I'm also not sure what the issue when dropping columns is in alembic, adding "mssql_drop_default=True" to the drop_column() directive takes care of it.

  2. Log in to comment