Auto increment non primary column in SQLAlchemy

Question

In my db model I need userId and forumPostId to be a composite primary key. But I need id to be a auto incremented value. But when I'm trying to insert new row in table getting none in id instead of a auto incremented integer.

class ForumPostFollow(db.Model):
    __tablename__ = "forum_post_follow"
    id = db.Column(db.Integer,autoincrement=True,nullable=False,unique=True)
    userId = db.Column(db.Integer,db.ForeignKey('user.id'),primary_key=True)
    forumPostId = db.Column(db.Integer,db.ForeignKey('forum_post.id'),primary_key=True)
    active = db.Column(db.Boolean,nullable=False)

My package versions

Flask-SQLAlchemy==2.3.2 SQLAlchemy>=1.3.0

This question is similar to this question. But it's for version 1.1

Updated Question

I've changed my id columns sequence from terminal

ALTER TABLE forum_post_follow DROP COLUMN id;
ALTER TABLE forum_post_follow ADD COLUMN id SERIAL;

Then my altered columns looks like this

But still getting same error

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (1, 1, t, null).

[SQL: INSERT INTO forum_post_follow (id, "userId", "forumPostId", active) VALUES (%(id)s, %(userId)s, %(forumPostId)s, %(active)s)]
[parameters: {'id': None, 'userId': 1, 'forumPostId': 1, 'active': True}]

See here [autoincrement](https://docs.sqlalchemy.org/en/13/core/metadata.html?highlight=autoincrement#sqlalchemy.schema.Column.params.autoincrement). Basically it won't work unless column is part of a `primary key` or you have set up the field as `serial/IDENTITY` manually on the server. — Adrian Klaver, Nov 28 '20 at 16:04
@AdrianKlaver can you please have a look in my updated question — Shahrear Bin Amin, Nov 28 '20 at 17:39
`'id': None` means you are trying to pass `NULL` into the `id` column. That trips the `NOT NULL` constraint. Leave the `id` field out of the `INSERT` and Postgres will automatically add the value. — Adrian Klaver, Nov 28 '20 at 17:44
@AdrianKlaver I have the same problem as the author of this question. Had the same idea as you. But how do I leave out the id field? I tried this "del db_obj['id']" before using session.add(db_obj). Unfortunately this gives me the following error: "'Job' object does not support item deletion" — Jabb, Dec 03 '20 at 21:38
@Jabb that is going to need its own question as you will need to supply more information about your model, query, table schema, SQLAlchemy version, etc. — Adrian Klaver, Dec 03 '20 at 22:28
have you found a workaround solution on this? I encountered the same issue with you. I can alter the table in DB manually, but the main question is how to bypass sqlalchemy not null checker — Ziqi Liu, Jun 08 '21 at 04:36

score 0 · Answer 1 · answered Mar 29 '23 at 20:22

I found a workaround for psql - I created the table, then added the serial column manually. If you set the default value to the nextval() function and refer to the sequence ({schema}.{table}_{col-name}_seq), then it will explicitly call for the next value on insert as opposed to that happening behind the scenes, which gets it working. Not ideal, but far from terrible

ALTER TABLE public.autoincrement ADD COLUMN non_pkey_auto_inc SERIAL;

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import func

db = SQLAlchemy()

class MyTable(db.Model):
    __tablename__ = "autoincrement"
    __table_args__ = "public"
    id = db.Column(db.Integer(), nullable=False, autoincrement=True, primary_key=True)
    non_pkey_auto_inc = db.Column(db.Integer(), default=func.nextval(f"{__table_args__['schema']}.{__tablename__}_non_pkey_auto_inc_seq"))

score 0 · Answer 2 · answered May 27 '23 at 09:16

Extending @JellyBeans answer, For SQLAlchemy version 1.4, you can do something like this. Just take care that you don't insert value in non_pkey column so that postgres picks default value which is auto incrementing.


from sqlalchemy import Table, Integer, VARCHAR, Column, Sequence, func, MetaData

metadata = MetaData()

seq_non_pkey_auto_inc = Sequence('non_pkey_auto_inc_seq', metadata=metadata)
n_tbl_test = 'tbl_test'
s_tbl_entity = Table(
    n_tbl_test, metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('non_pkey', Integer, nullable=False, server_default=func.nextval('non_pkey_auto_inc_seq')),
    Column('col1', VARCHAR(100), nullable=False),
    Column('col2', VARCHAR(50), nullable=False)
)

score -2 · Answer 3 · answered Dec 04 '20 at 07:29

I had a similar problem and found that as per SQLAlchemy 1.3 Documentation:

For the case where this default generation of IDENTITY is not desired, specify False for the Column.autoincrement flag, on the first integer primary key column:

so you might want to try this:

class ForumPostFollow(db.Model):
    __tablename__ = "forum_post_follow"
    id = db.Column(db.Integer,autoincrement=False,primary_key=True,nullable=False,unique=True)
    userId = db.Column(db.Integer,db.ForeignKey('user.id'),primary_key=True)
    forumPostId = db.Column(db.Integer,db.ForeignKey('forum_post.id'),primary_key=True)
    active = db.Column(db.Boolean,nullable=False)

@ZainQasmi multiple primary_keys will create a composite key. — avizzzy, Mar 29 '23 at 21:55

Auto increment non primary column in SQLAlchemy

3 Answers3