Is it possible to get sqlalchemy to create a composite primary key with an integer part without creating an IDENTITY type?

Question

I am using sqlalchemy 6.0. In the SQL Server dialect of T-SQL, it seems like you need to make any integer that is part of my primary key into an identity. It might be ok if the whole field was the main key, but my composite, and that won't work for me. Is there any way to suppress this behavior?

Here is a demonstration of the problem:

from sqlalchemy import * from sqlalchemy.schema import CreateTable from sqlalchemy.types import CHAR import sqlalchemy.dialects.mssql.pyodbc metadata = MetaData() t = Table('TEST', metadata, Column(u'int_part', Integer, primary_key=True, nullable=False), Column(u'char_part', CHAR(length=20), primary_key=True, nullable=False), ) create = CreateTable(t) print "Generic dialect gets it right" print create.compile() print print "MSSql dialect gets it wrong" print create.compile(dialect=sqlalchemy.dialects.mssql.pyodbc.dialect()) 

Results:

 Generic dialect gets it right CREATE TABLE "TEST" ( int_part INTEGER NOT NULL, char_part CHAR(20) NOT NULL, PRIMARY KEY (int_part, char_part) ) SQL Server T-SQL dialect gets it wrong CREATE TABLE [TEST] ( int_part INTEGER NOT NULL IDENTITY(1,1), char_part CHAR(20) NOT NULL, PRIMARY KEY (int_part, char_part) ) 

Answer 1

I have the same problem. The solution is to add autoincrement = False to the int primary key column constructor:

 Column(u'int_part', Integer, primary_key=True, nullable=False, autoincrement=False) 

Otherwise, sqlalchemy assumes that it should make it an identity column.