Bug with MSSQL backend and multiple primary key columns, when creating tables using MetaData

[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.

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])
)

[rekcahpassyla]

If I swap the order of the columns in the code

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('Month', sa.Integer, sa.CheckConstraint("Month >= 1 AND Month <= 12"), primary_key=True),
    sa.Column('DayOfWeek', sa.Integer, sa.CheckConstraint("DayOfWeek >= 0 AND DayOfWeek <= 8"), primary_key=True),
    sa.Column('Key4', sa.Integer, primary_key=True),
    sa.Column('Value', sa.Float(53), nullable=False),
)

metadata.create_all()

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.

CREATE TABLE [MYTABLE] (
        [DateInsertedUTC] DATETIME NOT NULL, 
        [Key1] VARCHAR(32) NOT NULL, 
        [Key2] VARCHAR(64) NOT NULL, 
        [Key3] VARCHAR(2) NOT NULL, 
        [Month] INTEGER NOT NULL IDENTITY(1,1) CHECK (Month >= 1 AND Month <= 12), 
        [DayOfWeek] INTEGER NOT NULL CHECK (DayOfWeek >= 0 AND DayOfWeek <= 8), 
        [Key4] INTEGER NOT NULL, 
        [Value] FLOAT(53) NOT NULL, 
        PRIMARY KEY ([DateInsertedUTC], [Key1], [Key2], [Key3], [Month], [DayOfWeek], [Key4])
)

[Mike Bayer]

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.

[Mike Bayer]

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.

[rekcahpassyla]

Many thanks!

[Mike Bayer]

• write some really complete docs on MSSQL autoincrement, fixes #3094

→ <<cset c55e519764b0>>

[Mike Bayer]

• write some really complete docs on MSSQL autoincrement, fixes #3094

→ <<cset 02b0af4f7ce2>>