naming_convention doesn't support MS SQL DEFAULT constraints

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)