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

Issue #3094 resolved
rekcahpassyla
created an issue

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)

  1. rekcahpassyla reporter

    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])
    )
    
  2. Michael Bayer repo owner

    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.

  3. Log in to comment