autoincrement for primary key not working in sqlalchemy-CodePudding

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(String, primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))

    children = relationship("UserAndGroup")

class Group(Base):
    __tablename__ = "groups"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))

    children = relationship("UserAndGroup")

class UserAndGroup(Base):
    __tablename__ = "groups_users"
    user_id = Column(Integer, ForeignKey("users.id"), primary_key=True)
    group_id = Column(Integer, ForeignKey("groups.id"), primary_key=True)

I have attached my models above.

While inserting a user:

session.add(User(user_id="johnny123", first_name="John", last_name="Don"))

I get the following error:

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

I am using the following packages:

SQLAlchemy   1.4.29
psycopg2     2.9.2

I went through the documentation, and it says autoincrement is a default behavior for primary keys.

source: https://docs.sqlalchemy.org/en/14/core/metadata.html?highlight=autoincrement#sqlalchemy.schema.Column.params.autoincrement

I am probably doing something wrong, thanks for helping out.

CodePudding user response：

tl;dr Your tables already exist. SQLalchemy won't recreate them. So your changes to the model schema have no effect. Check what your actual model is in Postgres.

If you want to change the schema of an existing table you will either have to drop and recreate the tables, losing the data, or migrate them.

Your UserAndGroup model is invalid. Users has a compound primary key on (id, user_id) but UserAndGroup is trying to refer to ForeignKey("users.id"). When you try to create this table it should result in an error.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "users"

But if the tables already exist SQLalchemy will not try to recreate them and no error.

That composite primary key does not appear to serve a purpose. users.id is already unique. If you want to make sure user_id is unique don't make it part of the primary key, declare it unique.

And probably name it username to avoid confusing it with a foreign key to the users table.