IntegrityError: foreign key violation upon delete

Question

I have Order and Shipment model. Shipment has a foreign key to Order.

class Order(...):
   ...

class Shipment()
   order = m.ForeignKey('Order')
   ...

Now in one of my views I want do delete order object along with all related objects. So I invoke order.delete().

I have Django 1.0.4, PostgreSQL 8.4 and I use transaction middleware, so whole request is enclosed in single transaction.

The problem is that upon order.delete() I get:

...
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/__init__.py", line 28, in _commit
return self.connection.commit()

IntegrityError: update or delete on table "main_order" violates 
foreign key constraint "main_shipment_order_id_fkey" on table "main_shipment"
DETAIL:  Key (id)=(45) is still referenced from table "main_shipment".

I checked in connection.queries that proper queries are executed in proper order. First shipment is deleted, after that django executes delete on order row:

{'time': '0.000', 'sql': 'DELETE FROM "main_shipment" WHERE "id" IN (17)'},
{'time': '0.000', 'sql': 'DELETE FROM "main_order" WHERE "id" IN (45)'}

Foreign key have ON DELETE NO ACTION (default) and is initially deferred. I don't know why I get foreign key constraint violation.

I also tried to register pre_delete signal and manually delete shipment objects before delete on order is called, but it resulted in the same error.

I can change ON DELETE behaviour for this key in Postgres but it would be just a hack, I wonder if anyone has a better idea what's going on here.

There is also a small detail, my Order model inherits from Cart model, so it actually doesn't have id field but cart_ptr_id and after DELETE on order is executed there is also DELETE on cart, but it seems unrelated? to the shipment->order problem so I simplified it in the example.

Answer 1

DETAIL: Key (id)=(45) is still referenced from table "main_shipment".

There is still a record referencing to id 45. You did delete record 17 in main_shipment before, but there might be others as well. You have to delete all records in main_shipment referencing to id 45 in main_order. If not, the database protects you from doing harm to your data.