[Sqlalchemy-tickets] Issue #3011: naming_convention doesn't support MS SQL DEFAULT constraints (zzzeek/sqlalchemy)

[Marek B. <iss...@bi...>]

New issue 3011: naming_convention doesn't support MS SQL DEFAULT constraints
https://bitbucket.org/zzzeek/sqlalchemy/issue/3011/naming_convention-doesnt-support-ms-sql

Marek Baczyński:

SQLAlchemy 0.9.4, Python 3.4.0, MS SQL 2008 R2

This causes issues when dropping columns in alembic.

Table definition:


```
#!python

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:


```
#!sql
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:


```
#!sql

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:


```
#!sql

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