Getting SQLite INTEGER PRIMARY KEY for a column

[Richard Damon]

I am using SQLAlchemy ORM with a SQLite database, and many of my tables
will have a simple integer primary key, with lots of foreign keys
referencing them. It should improve efficiency if that integer primary
key was the alias for the ROWID that you get by defining the column as
INTEGER PRIMARY KEY, but it seems that with a definition of:


class Language(Base):
    """Define Language Table."""

    __tablename__ = "Language"
    lang_id = Column(Integer, primary_key=True)
    lang_code = Column(String(20), unique=True)

I get as the DDL:

CREATE TABLE "Language" (
    lang_id INTEGER NOT NULL,
    lang_code VARCHAR(20),
    CONSTRAINT "pk_Language" PRIMARY KEY (lang_id),
    CONSTRAINT "uq_Language_lang_code" UNIQUE (lang_code)
)

which does not (at least appear to) create the needed primary key that
is an alias for the ROWID. I can't seem to find anything documented to
do to make this happen. I would think this would be a commonly wanted
optimization. Is there something I can do to get this? I would like to
be able to use the ORM.

--
Richard Damon

[Mike Bayer]

On Wed, Jul 1, 2020, at 9:52 PM, Richard Damon wrote:

I am using SQLAlchemy ORM with a SQLite database, and many of my tables

will have a simple integer primary key, with lots of foreign keys

referencing them. It should improve efficiency if that integer primary

key was the alias for the ROWID that you get by defining the column as

INTEGER PRIMARY KEY, but it seems that with a definition of:

class Language(Base):

    """Define Language Table."""

    __tablename__ = "Language"

    lang_id = Column(Integer, primary_key=True)

    lang_code = Column(String(20), unique=True)

I get as the DDL:

CREATE TABLE "Language" (

    lang_id INTEGER NOT NULL,

    lang_code VARCHAR(20),

    CONSTRAINT "pk_Language" PRIMARY KEY (lang_id),

    CONSTRAINT "uq_Language_lang_code" UNIQUE (lang_code)

)

which does not (at least appear to) create the needed primary key that

is an alias for the ROWID.

I think you might be reading that phrase (not sure where you read it) too literally.  per https://www.sqlite.org/lang_createtable.html#rowid

> With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key".

> The exception mentioned above is that if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key.

the primary key above is not DESC so it's covered as a synonym for ROWID, and will generate incrementing integer values automatically.  as long as you are getting those incrementing keys, and you arent using the AUTOINCREMENT keyword, you are using the rowid.

I can't seem to find anything documented to

do to make this happen. I would think this would be a commonly wanted

optimization. Is there something I can do to get this? I would like to

be able to use the ORM.

-- 

Richard Damon

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c4b955a6-1dda-7fab-49a0-c84f2603822c%40Damon-Family.org.

[Richard Damon]

On 7/1/20 10:12 PM, Mike Bayer wrote:
>
> I think you might be reading that phrase (not sure where you read it)
> too literally.  per https://www.sqlite.org/lang_createtable.html#rowid
>
> > With one exception noted below, if a rowid table has a primary key
> that consists of a single column and the declared type of that column
> is "INTEGER" in any mixture of upper and lower case, then the column
> becomes an alias for the rowid. Such a column is usually referred to
> as an "integer primary key".
>
> > The exception mentioned above is that if the declaration of a column
> with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it
> does not become an alias for the rowid and is not classified as an
> integer primary key.
>
> the primary key above is not DESC so it's covered as a synonym for
> ROWID, and will generate incrementing integer values automatically. 
> as long as you are getting those incrementing keys, and you arent
> using the AUTOINCREMENT keyword, you are using the rowid.
>
>

Looks like you may be right. I took the restriction to mean that the
PRIMARY KEY designation had to be on the row definition, and not as a
separate constraint, and DBeaver was showing a unique index being
defined for the primary key column, but the CLI for sqlite3 doesn't show
that index, and when manually inserting data such that the ROWID would
be different if it wasn't an alias shows it following the inserted data,
so I guess it is becoming the named alias for the ROWID.

I think I came across a different section wording that restriction that
wasn't as clear about it, or it was long enough ago that they have
updated that wording to be clearer.

--
Richard Damon