[Sqlalchemy-tickets] Issue #3011: naming_convention doesn't support MS SQL DEFAULT constraints (zzz
Brought to you by:
zzzeek
|
From: Marek B. <iss...@bi...> - 2014-03-31 10:46:37
|
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 ``` |