Databases
StackOverflow2013
Postsdbo
POPrevent Django from updating identity column in MSSQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPrevent Django from updating identity column in MSSQL
    primarykey
    data
    text
    <p>I'm working with a legacy DB in MSSQL. We have a table that has two columns that are causing me problems: </p> <pre><code>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. </code></pre> <p>So the table has the ssn column as primary key and the relevant part of the SQL-update code generated by django is this: </p> <pre><code>UPDATE [EMP] SET [EMPID] = 399, ......... WHERE [EMP].[SSN] = 2509882579 </code></pre> <p>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: </p> <pre><code>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)") </code></pre> <p>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?</p> <p><strong>EDIT</strong><br> I've patched my model up like this: </p> <pre><code>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) </code></pre> <p>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!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfzzylogic
    1. USSindri Guðmundsson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POPrevent Django from updating identity column in MSSQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPrevent Django from updating identity column in MSSQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI know nothing about django so I might be way of here but have you tried to use `Autofield`. Something like this `empid = models.AutoField( ...`.
      singulars
      1. POPrevent Django from updating identity column in MSSQL
      1. USMikael Eriksson
    2. CO`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 :(
      singulars
      1. POPrevent Django from updating identity column in MSSQL
      1. USSindri Guðmundsson
    3. COI 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?
      singulars
      1. POPrevent Django from updating identity column in MSSQL
      1. USHardCode
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload