0

I'm using a API payload to update a database. I have some business rules enforced by Foreign Key Constraints, like: Address must have a Customer ID associated to it (One Customer <-> Many Address').

When updating the database, I have to make sure that all or nothing is committed, in order not to update one table and update another, so I write the update step by step:

  1. Update customers
  2. Update address
  3. Commit all changes

Basically I use a list of classes instances to call add_all(), and just committing after both add_all. Where id is the PK on customers, and the FK on address.

customers_list = []
customer_entry = Customers(name=name, id=id)
customer_list.append(entries)
db.session.add_all(customer_list)

address_list = []
address_entry = Address(id=customer_id, zip=zip)
address_list.append(entries)
db.session.add_all(address_list)

db.session.commit()

The thing is, when it gets to address', it gives me an Integrity Error, since it can't find the customer_idon the Customers table (since it hasn't been committed).

I though about disabling the Foreign Key Constraing for a while, but it doesn't seem a good way to ensure a solid database.

How do I maintain consistency while avoiding integrity errors (FK constraint)?

lowercase00
  • 1,340
  • 2
  • 12
  • 29
  • Does `customers.add_all()` call `db.session.add_all()`? Please post a [minimal, complete, verifiable example](https://stackoverflow.com/help/mcve) so we can try to reproduce the problem. – unutbu Apr 12 '19 at 12:23
  • Sorry if that wasn’t clear, I’ve updated the snipped to help reproduce the problem. But after reading the other link, I’m starting to think it’s just not possible without commit... – lowercase00 Apr 12 '19 at 13:02
  • Your situation sounds somewhat similar to this [example from the docs](https://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.html#rows-that-point-to-themselves-mutually-dependent-rows). Perhaps you can pattern your code according to that example. – unutbu Apr 12 '19 at 13:17
  • Thanks for the link. This is somewhat above my skill level, I’ll try to read it carefully to understand what are the possible workarounds. In the mean time I chose to commit the customers, but still doesn’t sound like good practice... – lowercase00 Apr 13 '19 at 09:13

0 Answers0