CRUD
Started off writing my own SQL and using Django’s RAW Execute to run it, but quickly decided I’d rather use the Django-centric ORM to manipulate the db.
Went absolutely doo-lally with repeated integrity errors, seemingly caused by Django trying to insert on an identity field. Took me far too long to check that the primary key on the database table actually was an identity field. Turned out it wasn’t!
Remade the table and now the Django model data type AutoField actually works as expected.
- if a primary key is on database table as
identity(1,1)the value is generated by the database sequentially - if that same primary key is defined in the Django model as
AutoFieldDjango will let the value be generated by the database - and if noone knows who should be generating the primary key values unhappiness ensues…
Updates are passed to model.save(forced_insert=True) without a primary key assignation because I want to maintain the existing Type 2 SDC on data in Prism.
After each insert we then run an update on the pre-existing record to set ValidTo to now(), effecting the logical delete.
From the docs: https://docs.djangoproject.com/en/5.1/ref/models/instances/
If the object’s primary key attribute is set to anything except None, Django executes an UPDATE.
If the object’s primary key attribute is not set or if the UPDATE didn’t update anything (e.g. if primary key is set to a value that doesn’t exist in the database), Django executes an INSERT.
So when POSTing project updates the pID primary key field is omitted from the model object that is then used for the .save() action, and when logically deleting the previous record it is just one of the two fields used (pID & ValidTo).