Bug with MSSQL backend and multiple primary key columns, when creating tables using MetaData
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.
I looked in http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html but could not find any indication that this might be a problem.
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:
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)
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
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:
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])
)
Comments (8)
-
reporter -
reporter - edited description
-
reporter - changed component to mssql
-
repo owner - changed status to closed
hi -
the behavior you're seeing is described at http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement.
use autoincrement=False on all integer primary key column to prevent it from being considered for IDENTITY:
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, autoincrement=False), sa.Column('Month', sa.Integer, sa.CheckConstraint("Month >= 1 AND Month <= 12"), primary_key=True, autoincrement=False), sa.Column('Key4', sa.Integer, primary_key=True, autoincrement=False), sa.Column('Value', sa.Float(53), nullable=False), )
I understand that this particular table is a bad match for SQLA's default behavior; the defaults are chosen this way to suit the vast majority of typical cases, that is tables that have a simple integer primary key.
-
repo owner I can see that our docs at http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#auto-increment-behavior are lacking in pointing this out, I'll add this now.
-
reporter Many thanks!
-
repo owner - changed status to resolved
- write some really complete docs on MSSQL autoincrement, fixes
#3094
→ <<cset c55e519764b0>>
-
repo owner - write some really complete docs on MSSQL autoincrement, fixes
#3094
→ <<cset 02b0af4f7ce2>>
- write some really complete docs on MSSQL autoincrement, fixes
- Log in to comment
If I swap the order of the columns in the code
the Month column is now created as IDENTITY(1,1), showing it's always the first integer primary key column that gets created as the identity.