In Django, why is migration to Oracle resulting in attempts to save instances with null ID?

Question

I'm migrating a Django project from SQLite to Oracle, and I'm getting an error thrown on the line disn_requisition.save() claiming that it has a null ID. I have not attempted to manually set or fiddle with id fields on any model, although I do read them.

Any insight on what I need to do to address this?

IntegrityError at /upload/storage

ORA-01400: cannot insert NULL into ("INVDB"."INVDB_DISK_REQUISITION"."ID")
Request Method: POST
Request URL: [url here]
Django Version: 1.2.3
Exception Type: IntegrityError
Exception Value: 
ORA-01400: cannot insert NULL into ("INVDB"."INVDB_DISK_REQUISITION"."ID")
Exception Location: /tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/django/db/backends/oracle/base.py in execute, line 507
Python Executable: /tools/python/2.7/Linux_x86_64/bin/python
Python Version: 2.7.0
Python Path: ['/home/jhayward/invdb', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/django_filter-0.5.3-py2.7.egg', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/ez_setup-0.9-py2.7.egg', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/distribute-0.6.14-py2.7.egg', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/hgsvn-0.1.8-py2.7.egg', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/paramiko-1.7.6-py2.7.egg', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/pycrypto-2.3-py2.7-linux-x86_64.egg', '/tools/python/cx_Oracle/10g/2.6/Linux_x86_64/lib/python2.6/site-packages', '/home/jhayward', '/home/jhayward/invdb/HOME_DIRECTORY', '/home/jhayward/invdb/HOME_DIRECTORY/invdb', '/tools/python/2.7/Linux_x86_64/lib/python27.zip', '/tools/python/2.7/Linux_x86_64/lib/python2.7', '/tools/python/2.7/Linux_x86_64/lib/python2.7/plat-linux2', '/tools/python/2.7/Linux_x86_64/lib/python2.7/lib-tk', '/tools/python/2.7/Linux_x86_64/lib/python2.7/lib-old', '/tools/python/2.7/Linux_x86_64/lib/python2.7/lib-dynload', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages', '/tools/python/2.7/Linux_x86_64/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg-info']
Server time: Thu, 30 Dec 2010 10:04:24 -0600

Just to clarify, the error you've included is when creating and saving a brand new Model? Perhaps you want to post the Model definition as well? — Dwight Gunning, Dec 30 '10 at 16:31
It occurs when I am trying to save a brand new instance of a Model. — Christos Hayward, Dec 30 '10 at 16:50
The project works as well with PostgreSQL as SQLite as far as I can tell. — Christos Hayward, Dec 30 '10 at 17:44

score 3 · Accepted Answer · answered Feb 07 '11 at 13:32

I posted this question to django-users@googlegroups.com and got the following answer:

"The problem is in the difference in SQLite and Oracle. Unlike MySQL, PostgresQL, and SQLIte, Oracle doesn't have auto-generated primary keys. To make up for that, Django on Oracle uses a trigger for each Django-managed table that recognizes a NULL primary key on insert, and generates a key by using a dedicated sequence for that table. Usually if you're creating the tables from scratch, the Django management tools (syncdb, sqlall, etc) will create the sequence and trigger for you. Without knowing more about how your populated your schema, it's hard to say what went wrong, but if you run 'python manage.py sqlall invdb' it should print out the DDL to create the trigger and sequence, and you can manually add them."

In my case--and the error may be secondary damage related to this--"python manage.py syncdb" crashed the first time through, and as the standard workaround I ran it a second time, when it ran without reported error. I handed this off to another person so I'm not sure how it got resolved, but I believe that the missing trigger may be secondary damage related to the crash, and I imagine this was resolved by having a database type put code in place so the offending model had the trigger.

+1 for the mention of the failure of the syncdb the first time around. That's what did it for me. Thanks! — Shawn, Jun 13 '13 at 14:37
Note: This 'pseudo-worked' for me. Trying it twice ran without obvious failures, but then there were errors surrounding what in every other Django-supported database are autoincrement primary keys: Oracle doesn't have them natively, so IF it works, Django compensates by adding posttriggers that emulate autoincrement primary keys. This compensation for a missing feature was a casualty of Oracular incompatibility, and didn't work. If you get it working by running it twice, great, but I would be cautious about things working until you've tested them out. — Christos Hayward, Jun 13 '13 at 20:44
I didn't make it clear that realizing the failure of the syncdb meant I needed to (in my case) fix the sequence (I was getting duplicate id errors after inserting rows, not null id errors). The trigger was working fine, but the sequence had the wrong next_val. Once I manually corrected that, everything started working as designed. If not for mentioning the syncdb failure, I might not have discovered the underlying cause. Thanks for clarifying for others what the underlying cause really is. — Shawn, Jun 14 '13 at 20:30

In Django, why is migration to Oracle resulting in attempts to save instances with null ID?

1 Answers1