[Sqlalchemy-tickets] Issue #3094: Bug with MSSQL backend and multiple primary key columns, when cr
Brought to you by:
zzzeek
|
From: rekcahpassyla <iss...@bi...> - 2014-06-24 08:34:48
|
New issue 3094: Bug with MSSQL backend and multiple primary key columns, when creating tables using MetaData https://bitbucket.org/zzzeek/sqlalchemy/issue/3094/bug-with-mssql-backend-and-multiple rekcahpassyla: Before submitting this I tried to search for an existing issue - https://bitbucket.org/zzzeek/sqlalchemy/issues?q=primary_key+identity did not return any results. Apologies, I don't know how to categorise this so I have left the default settings as Kind=bug, Priority=major, Milestone=0.9.5. OS: Windows 7 Enterprise Service Pack 1 Python version: Python 2.7.5 |Anaconda 1.9.0 (64-bit)| (default, Jul 1 2013, 12:37:52) [MSC v.1500 64 bit (AMD64)] SQLAlchemy version: https://github.com/zzzeek/sqlalchemy/commit/ea54b635d66bc695c5149ede5279cc6ee2f43e7c (I built a conda package from this) Target database: SQL Server 11.0.5058 Driver: SQL Server Native Client 11.0 Version 2011.110.3000.00 Using the sqlalchemy.MetaData class, I have been trying to create a table with a multiple-column primary key. I observed that the first integer column with primary_key=True is created as column type IDENTITY(1, 1). Here are some code samples. Many thanks. Using sqlite backend: ```python import sqlalchemy as sa engine = sa.create_engine(r'sqlite:///:memory:',echo=True) metadata = sa.MetaData(bind=engine) table = sa.Table( 'MYTABLE', metadata, sa.Column('DateInsertedUTC', sa.DateTime, primary_key=True, default=sa.func.sysutcdatetime()), sa.Column('Key1', sa.String(32), primary_key=True), sa.Column('Key2', sa.String(64), primary_key=True), sa.Column('Key3', sa.String(2), primary_key=True), sa.Column('DayOfWeek', sa.Integer, sa.CheckConstraint("DayOfWeek >= 0 AND DayOfWeek <= 8"), primary_key=True), sa.Column('Month', sa.Integer, sa.CheckConstraint("Month >= 1 AND Month <= 12"), primary_key=True), sa.Column('Key4', sa.Integer, primary_key=True), sa.Column('Value', sa.Float(53), nullable=False), ) metadata.create_all() ``` generates the following SQL (from the echo trace) ```sql CREATE TABLE "MYTABLE" ( "DateInsertedUTC" DATETIME NOT NULL, "Key1" VARCHAR(32) NOT NULL, "Key2" VARCHAR(64) NOT NULL, "Key3" VARCHAR(2) NOT NULL, "DayOfWeek" INTEGER NOT NULL CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), "Month" INTEGER NOT NULL CHECK (Month >= 1 AND Month <= 12), "Key4" INTEGER NOT NULL, "Value" FLOAT NOT NULL, PRIMARY KEY ("DateInsertedUTC", "Key1", "Key2", "Key3", "DayOfWeek", "Month", "Key4") ) ``` The following code using mssql backend ```python import sqlalchemy as sa engine = sa.create_engine(r'mssql://redacted;TrustedConnection=Yes',echo=True) metadata = sa.MetaData(bind=engine) table = sa.Table( 'MYTABLE', metadata, sa.Column('DateInsertedUTC', sa.DateTime, primary_key=True, default=sa.func.sysutcdatetime()), sa.Column('Key1', sa.String(32), primary_key=True), sa.Column('Key2', sa.String(64), primary_key=True), sa.Column('Key3', sa.String(2), primary_key=True), sa.Column('DayOfWeek', sa.Integer, sa.CheckConstraint("DayOfWeek >= 0 AND DayOfWeek <= 8"), primary_key=True), sa.Column('Month', sa.Integer, sa.CheckConstraint("Month >= 1 AND Month <= 12"), primary_key=True), sa.Column('Key4', sa.Integer, primary_key=True), sa.Column('Value', sa.Float(53), nullable=False), schema='REDACTED' ) metadata.create_all() ``` generates the following SQL: ```sql CREATE TABLE [REDACTED].[MYTABLE] ( [DateInsertedUTC] DATETIME NOT NULL, [Key1] VARCHAR(32) NOT NULL, [Key2] VARCHAR(64) NOT NULL, [Key3] VARCHAR(2) NOT NULL, [DayOfWeek] INTEGER NOT NULL IDENTITY(1,1) CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), [Month] INTEGER NOT NULL CHECK (Month >= 1 AND Month <= 12), [Key4] INTEGER NOT NULL, [Value] FLOAT(53) NOT NULL, PRIMARY KEY ([DateInsertedUTC], [Key1], [Key2], [Key3], [DayOfWeek], [Month], [Key4]) ) ``` and the desired SQL was ``` CREATE TABLE [REDACTED].[MYTABLE] ( [DateInsertedUTC] DATETIME NOT NULL, [Key1] VARCHAR(32) NOT NULL, [Key2] VARCHAR(64) NOT NULL, [Key3] VARCHAR(2) NOT NULL, [DayOfWeek] INTEGER NOT NULL CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), [Month] INTEGER NOT NULL CHECK (Month >= 1 AND Month <= 12), [Key4] INTEGER NOT NULL, [Value] FLOAT(53) NOT NULL, PRIMARY KEY ([DateInsertedUTC], [Key1], [Key2], [Key3], [DayOfWeek], [Month], [Key4]) ) ``` |