Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I'm using sqlalchemy 6.0. The SQL Server T-SQL dialect seems to want to make any integer that's part of my primary key into an identity. That may be ok if the integer field were the primary key, but mine is a composite and this isn't going to work for me. Is there a way to suppress this behavior?

Here's 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)
)
like image 222
Mark Wright Avatar asked Feb 03 '23 05:02

Mark Wright


1 Answers

I got bit by 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 it should make it an identity column.

like image 141
David Haley Avatar answered Feb 06 '23 14:02

David Haley