Auto increment non primary column in SQLAlchemy

user_11584728 0
Viewed 2493

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

enter image description here

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}]
flask-sqlalchemy sqlalchemy postgresql orm
1 Answers

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)
user_12669850
Related