How to fix IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table “users” violates foreign key constraint

I have two tables created with Flask-SQLAlchemy below – they have a one to one relationship.

class Logo(db.Model):
    __tablename__ = "logo"
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(100))
    data = db.Column(db.LargeBinary)
    username = db.Column(db.String(100), db.ForeignKey("users.username"))

    users = db.relationship("User", backref=backref("logo", uselist=False))

    def __init__(self, filename: str, data, username: str):
        self.filename = filename
        self.data = data
        self.username = username

    def __repr__(self) -> str:
        return "<Logo (filename='{}', username='{}')>".format(
            self.filename, self.username
        )


class User(UserMixin, db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), unique=True)
    password = db.Column(
        db.String(200), primary_key=False, unique=False, nullable=False
    )
    is_admin = db.Column(db.Boolean, default=False, nullable=True)

    def __init__(
        self,
        username: str,
        password: str,
        is_admin: bool = False,
    ):
        self.username = username
        self.password = self.set_password(password)
        self.is_admin = is_admin

    def get_id(self):
        return self.username

    def set_password(self, password: str) -> str:
        return generate_password_hash(password, method="sha256")

    def check_password(self, password: str):
        return check_password_hash(self.password, password)

    def __repr__(self) -> str:
        return "<User {}>".format(self.username)

I would like to update the user table in a case when the user would like to have a new username:

user01 = User.query.filter_by(username="user01").first()
logo = Logo.query.filter_by(username="user01").first()

new_username= "newusertest"
user01.username = new_username
logo.users = user01

logo.username = new_username

db.session.add(user01)
db.session.add(logo)


db.session.commit()

The db.session.commit throws the following error:

IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "users" violates foreign key constraint "logo_username_fkey" on table "logo"
DETAIL:  Key (username)=(user01) is still referenced from table "logo".

[SQL: UPDATE users SET username=%(username)s WHERE users.id = %(users_id)s]
[parameters: {'username': 'newusertest', 'users_id': 2}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

The error says the logo table still has the old username but I have updated it and I don’t know why that shows up again, I have spent the last 2 hours debugging and trying different stuff but nothing works.

Answer

You could temporarily make the foreign key constraint deferrable and make the update in psql. Say we have these tables:

test# d parent
                              Table "public.parent"
 Column │       Type        │ Collation │ Nullable │           Default            
════════╪═══════════════════╪═══════════╪══════════╪══════════════════════════════
 id     │ integer           │           │ not null │ generated always as identity
 name   │ character varying │           │          │ 
Indexes:
    "parent_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "child" CONSTRAINT "child_pname_fkey" FOREIGN KEY (pname) REFERENCES parent(name)

test# d child
                               Table "public.child"
 Column │       Type        │ Collation │ Nullable │           Default            
════════╪═══════════════════╪═══════════╪══════════╪══════════════════════════════
 id     │ integer           │           │ not null │ generated always as identity
 pname  │ character varying │           │          │ 
Foreign-key constraints:
    "child_pname_fkey" FOREIGN KEY (pname) REFERENCES parent(name)

then the statements would be

test# alter table child alter constraint child_pname_fkey deferrable;
ALTER TABLE
SET CONSTRAINTS
test# begin;
BEGIN
test#* set constraints child_pname_fkey deferred;
SET CONSTRAINTS
test#* update child set pname = 'Alice' where id = 1;
UPDATE 1
test#* update parent set name = 'Alice' where id = 1;
UPDATE 1
test#* commit;
COMMIT
test# alter table child alter constraint child_pname_fkey not deferrable;
ALTER TABLE
test#

Deferring the constraint means updates are evaluated at the end of the transaction rather than immediately, so the the point of view of the database the columns are not out of sync.

The long term solution is to use users.id as the foreign key, as it is less likely to change.