Multiple primary keys in django project

Question

In my django project I am working with legacy database, which has a table with multiple primary keys. I am facing the problem where Django ORM incorrectly constructs SQL queries when I call save() method on some model instance.

For example, I have the following model:

class MyTable(models.Model):
    field_1 = models.IntegerField(primary_key=True)
    field_2 = models.BooleanField(default=False, null=False)
    field_3 = models.BooleanField(default=False, null=False)
    user = models.ForeignKey(
        CustomUser, models.DO_NOTHING, db_column='userid', primary_key=True)

    class Meta:
        managed = False
        db_table = 'my_table'
        unique_together = (('user', 'field_1'),)

I get the Model's instance like that: mytable_instance = MyTable.objects.all()[0]. Then I want to modify that instance:

mytable_instance.field_2 = True
mytable_instance.field_2.save()

However, Django ORM executes this query:

{'sql': 'UPDATE `my_table` SET `field_2` = 1, `field_3` = 0 WHERE `my_table`.`field_1` = 123', 'time': '0.000'}

Which is not correct, because it will set new values for field_2 and field_3 for all rows in my_table with field_1 = 123

How can I overcome that problem? I need a SQL query like this:

'UPDATE `my_table` SET `field_2` = 1, `field_3` = 0 WHERE `my_table`.`field_1` = 123 AND `my_table`.`user_id` = 1'

Model for legacy database table:

class MyTable(models.Model):
    field1_id = models.IntegerField(db_column='field1id', primary_key=True)
    is_favorite = models.BooleanField(db_column='isfavorite', default=False, null=False)
    is_admin = models.BooleanField(db_column='isadmin', default=False, null=False)
    role = models.IntegerField(default=USER_GUEST, null=False)
    field2 = models.BooleanField(null=False, default=True)
    field3 = models.BooleanField(null=False, default=True)
    is_active = models.BooleanField(db_column='isactive', null=False, default=True)

    user = models.ForeignKey(
        CustomUser, models.DO_NOTHING, db_column='userid', primary_key=True)

    DB_NAME = 'default'

    class Meta:
        managed = False
        db_table = 'mytable'
        unique_together = (('user', 'field1_id'),)

Queries:

>>> from web_services.apps.my_app.models import MyTable
>>> g = MyTable.objects.get(field1_id=12)
>>> g.is_active = True
>>> g.save()
>>> connection.queries[-1]
{'time': '0.000', 'sql': 'UPDATE `mytable` SET `isfavorite` = 0, `isadmin` = 1, `role` = 3, `field2` = 1, `field3` = 1, `isactive` = 1 WHERE `mytable`.`field1id` = 12'}

But I need:

{'time': '0.000', 'sql': 'UPDATE `mytable` SET `isfavorite` = 0, `isadmin` = 1, `role` = 3, `field2` = 1, `field3` = 1, `isactive` = 1 WHERE `mytable`.`field1id` = 12' AND `mytable`.`userid` = 1'}

This is hardly plausible. Please post your full code, I suspect there is some issue in your code that leads to this. — e4c5, May 13 '16 at 00:28

score 2 · Accepted Answer · answered May 13 '16 at 12:20

2

Let's look at these two queries:

UPDATE `mytable` 
SET `isfavorite` = 0, `isadmin` = 1, `role` = 3, `field2` = 1, `field3` = 1, `isactive` = 1 
WHERE `mytable`.`field1id` = 12

And

UPDATE `mytable` 
SET `isfavorite` = 0,`isadmin` = 1, `role` = 3, `field2` = 1, `field3` = 1, `isactive` = 1 
WHERE `mytable`.`field1id` = 12' AND `mytable`.`userid` = 1

The only difference between the two is the AND userid=1 bit.

It is indeed correct when you say that

it will set new values for field_2 and field_3 for all rows in my_table with field1_id = 12

There is exactly one such row. Why? because you have defined field_id to be a primary key. Which guarantees that there is one and only one row with field1_id = N where N is any integer.

So django query is behaving perfectly.

answered May 13 '16 at 12:20

e4c5

52,766
11
101
134

The problem, that it's a legacy database, and it has these possible combinations of `(field1_id, user)`: `(12, 1)`, `(12,2)`, `(12, 3)`, `(2, 1)`, `(3, 1)` and etc. So, with `'mytable'.'field1id' = 12` it updates all `(12, 1)`, `(12,2)`, `(12, 3)` – AmirM May 13 '16 at 14:09
So, my question probably should be how to set composite key? – AmirM May 13 '16 at 14:11
No not according the the model that you have posted. There **can not be (12, 1), (12,2), (12, 3)** – e4c5 May 14 '16 at 00:17
Its there. It's a legacy database. It had before me two primary keys. And the model I have posted is the one I got with `manage.py inspectdb` command. – AmirM May 14 '16 at 07:33
inspectdb sheds more light. In this case the model that has been generated is not the right one. What you have in the database is a composite primary key which django does not support. – e4c5 May 14 '16 at 08:02
As things stand my answer is correct for your original question. Why update behaves the way it does. I shall be most gratefull if you could mark it as correct. Then kindly post another question about the composite primary key. – e4c5 May 14 '16 at 08:02
But how can I overcome that problem? So far, I decided to migrate all data to another table with one primary key `id`, this way apparently django can handle queries correctly – AmirM May 15 '16 at 19:05
Here is new [question](http://stackoverflow.com/questions/37248712/working-with-composite-primary-key-in-django-project-with-legacy-database) . Please, help to edit it if you think it doesn't state the problem properly. – AmirM May 16 '16 at 07:25

Multiple primary keys in django project

1 Answers1