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
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 please help out!
CodePudding user response:
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.
CodePudding user response:
try to define cascade="all,delete" in users relationship