Prevent Django from updating identity column in MSSQL

Question

I'm working with a legacy DB in MSSQL. We have a table that has two columns that are causing me problems:

class Emp(models.Model):  
    empid = models.IntegerField(_("Unique ID"), unique=True, db_column=u'EMPID')  
    ssn = models.CharField(_("Social security number"), max_length=10, primary_key=True, db_column=u'SSN') # Field name made lowercase.

So the table has the ssn column as primary key and the relevant part of the SQL-update code generated by django is this:

UPDATE [EMP] SET [EMPID] = 399, 
......... 
WHERE [EMP].[SSN] = 2509882579

The problem is that EMP.EMPID is an identity field in MSSQL and thus pyodbc throws this error whenever I try to save changes to an existing employee:

ProgrammingError: ('42000', "[42000] [Microsoft][SQL Native Client][SQL Server]C
annot update identity column 'EMPID'. (8102) (SQLExecDirectW); [42000] [Microsof
t][SQL Native Client][SQL Server]Statement(s) could not be prepared. (8180)")

Having the EMP.EMPID as identity is not crucial to anything the program, so dropping it by creating a temporary column and copying, deleting, renaming seems like the logical thing to do. This creates one extra step in transferring old customers into Django, so my question is, is there any way to prevent Django from generating the '[EMPID] = XXX' snippet whenever I'm doing an update on this table?

EDIT
I've patched my model up like this:

def save(self, *args, **kwargs):
    if self.empid:
        self._meta.local_fields = [f for f in self._meta.local_fields if f.name != 'empid']
        super().save(*args, **kwargs)

This works, taking advantage of the way Django populates it's sql-sentence in django/db/models/base.py (525). If anyone has a better way or can explain why this is bad practice I'd be happy to hear it!

I know nothing about django so I might be way of here but have you tried to use `Autofield`. Something like this `empid = models.AutoField( ...`. — Mikael Eriksson, Aug 23 '11 at 09:13
`AutoField` must be the primary key for the model and for legacy DB support I have to use the SSN field as the primary key, so unfortunately that is not a option :( — Sindri Guðmundsson, Aug 23 '11 at 10:12
I know that this is a legacy application, but using SSN as a primary key is a bad idea. While not likely, it is still POSSIBLE that your app will one day receive the same SSN - recycled by Social Security Administration when someone is deceased - and your application will "break", at least in terms of data integrity. Can you just change the app to use EMPID as the PK? — HardCode, Aug 23 '11 at 15:02
@HardCode: That would be the optimal thing to do, but as all other tables use the ssn field to link to the Emp table, that could create more problems than it would solve. The good thing is that I live in Iceland which has a population of under 350.000 and each application instance holds only between 30 and 3000 employees, so the risk is very minimal, although it's there :) — Sindri Guðmundsson, Aug 23 '11 at 15:21
So the EMPID column is an identity column, but not the primary key? Is it referenced in any constraints or used in any reports? — Bryan, Aug 30 '11 at 15:12
@beargle; Yes to the first question, no to the second... This table is fubar... — Sindri Guðmundsson, Aug 31 '11 at 09:26
@Sindri: If the column isn't used in reports or other constraints, then it doesn't add value to entity. You could remove it from the database to avoid the extra code in the model. Sounds like it is crazy design, but we've all been forced to work with what we're given :) — Bryan, Aug 31 '11 at 14:08
@beargle; It really is, but it has some significance to the system, e.g. in some parts of the system it is required for authentication, so to some extent I need to use it. ATM my edit code works, and we're still way behind production on this version, so I'll keep thinking about it ;) — Sindri Guðmundsson, Sep 02 '11 at 09:16
Have you tried something as simple as removing the field from the model? I'm 99% sure that django will just match up the values based on name. I don't believe it will throw and error if there is an extra field in there (but, might be wrong here). It's worth a shot! :) — David S, Mar 08 '12 at 00:35
@David: That would work, but I need read access to the field. That is, when retrieving the model I need the field to be present (and I have to be able to filter QuerySets by it), but when updating/creating I can't have it. My solution went production 4 months ago and I haven't had any problems with it. Thanks for the suggestion though :) — Sindri Guðmundsson, Mar 16 '12 at 07:11

score 5 · Answer 1 · answered Jan 06 '14 at 16:03

This question is old and Sindri found a workable solution, but I wanted to provide a solution that I've been using in production for a few years that doesn't require mucking around in _meta.

I had to write a web application that integrated with an existing business database containing many computed fields. These fields, usually computing the status of the record, are used with almost every object access across the entire application and Django had to be able to work with them.

These types of fields are workable with a model manager that adds the required fields on to the query with an extra(select=...).

ComputedFieldsManager code snippet: https://gist.github.com/manfre/8284698

class Emp(models.Model):
    ssn = models.CharField(_("Social security number"), max_length=10, primary_key=True, db_column=u'SSN') # Field name made lowercase.

    objects = ComputedFieldsManager(computed_fields=['empid'])


# the empid is added on to the model instance
Emp.objects.all()[0].empid

# you can also search on the computed field
Emp.objects.all().computed_field_in('empid', [1234])

Thanks for the input! ComputedFieldsManagers looks nice but I'm afraid it won't cut it for my problem, as that would mean changes all across our codebase. Putting that aside, I really hope people coming here will go for your answer rather than my hack :) — Sindri Guðmundsson, Jan 19 '14 at 01:01

Prevent Django from updating identity column in MSSQL

1 Answers1

Linked