sqlite: multi-column index with one autoincrement

[Former user]

Heyho!

I'm trying to use a table like

  class Entry(DeclarativeBase):
      id = Column(Integer, autoincrement=True, primary_key=True)
      version = Column(Integer, primary_key=True, default=0)

sqlalchemy (I'm using 0.5.6 as packaged in Debian) creates the table (without any autoincrement) but then expects the id column to autoincrement on INSERT's, which it won't.

Where it gets hairy is that sqlalchemy apparently (tested: 3.6.20) supports autoincrement only on single column integer primary key columns.

(I'm coming from a PostgreSQL background, so the expected behaviour is what I'm used to in pg. Haven't tested with sqla yet, but on SQL level, I get: autoincrement if I don't specify a value on insert, but allow to specify a value when I want to create a new "version" for an existing "id".)

[Former user]

It seems as guest I can't do this... Can somebody please add [e-mail] as cc: thanks.

[Former user]

(original author: ged)

[Mike Bayer]

If you're looking for SQLite's AUTOINCREMENT, that's not what this flag does, and I believe the docs state as much (the docs in 0.6 do - check the site). SQLite doesn't support autoincrment on composite primary keys either.

Here's 0.6's doc just for reference (applies to 0.5 too):

        :param autoincrement: This flag may be set to ``False`` to 
          indicate an integer primary key column that should not be
          considered to be the "autoincrement" column, that is
          the integer primary key column which generates values 
          implicitly upon INSERT and whose value is usually returned
          via the DBAPI cursor.lastrowid attribute.   It defaults
          to ``True`` to satisfy the common use case of a table
          with a single integer primary key column.  If the table
          has a composite primary key consisting of more than one
          integer column, set this flag to True only on the 
          column that should be considered "autoincrement".

          The setting *only* has an effect for columns which are:

          * Integer derived (i.e. INT, SMALLINT, BIGINT)

          * Part of the primary key

          * Are not referenced by any foreign keys

          * have no server side or client side defaults (with the exception
            of Postgresql SERIAL).

          The setting has these two effects on columns that meet the
          above criteria:

          * DDL issued for the column will include database-specific
            keywords intended to signify this column as an
            "autoincrement" column, such as AUTO INCREMENT on MySQL,
            SERIAL on Postgresql, and IDENTITY on MS-SQL.  It does 
            *not* issue AUTOINCREMENT for SQLite since this is a
            special SQLite flag that is not required for autoincrementing
            behavior.

          * The column will be considered to be available as 
            cursor.lastrowid or equivalent, for those dialects which
            "post fetch" newly inserted identifiers after a row has
            been inserted (SQLite, MySQL, MS-SQL).  It does not have 
            any effect in this regard for databases that use sequences 
            to generate primary key identifiers (i.e. Firebird, Postgresql, 
            Oracle).