[Sqlalchemy-tickets] Issue #3094: Bug with MSSQL backend and multiple primary key columns, when creating tables using MetaData (zzzeek/sqlalchemy)

[rekcahpassyla <iss...@bi...>]

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