- marked as enhancement
- changed milestone to 0.9.xx
naming_convention doesn't support MS SQL DEFAULT constraints
Issue #3011
new
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)
-
repo owner -
repo owner - changed component to mssql
-
reporter ok, wasn't aware of the mssql default drop check feature. sorry for the noise.
-
repo owner no problem! this morning's ticket crush resolved faster than it seemed like it would :)
-
repo owner - changed milestone to 0.x.xx
-
repo owner - changed milestone to 1.x.xx
- Log in to comment
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.