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


I have two tables created with flask sqlalchemy below – it is 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 = 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__(
        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



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)s]
[parameters: {'username': 'newusertest', 'users_id': 2}]
(Background on this error at:

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 please help out!


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 │           │          │ 
    "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;
test# begin;
test#* set constraints child_pname_fkey deferred;
test#* update child set pname = 'Alice' where id = 1;
test#* update parent set name = 'Alice' where id = 1;
test#* commit;
test# alter table child alter constraint child_pname_fkey not deferrable;

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 as the foreign key, as it is less likely to change.

Answered By – snakecharmerb

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published