Working with a composite primary key in a django project with an outdated database

Question

I have an outdated database where some table contains a composite primary key. The model that I run with the manage.py inspectdb command is as follows.

 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) class Meta: managed = False db_table = 'mytable' unique_together = (('user', 'field1_id'),) 

I can receive data normally. However, the problem occurs when I want to run the save() command on some instance of the model. The execution of the django request is incorrect. For instance:

 >>> 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'} 

Since django does not support a composite primary key, what would be the best solution to solve this problem? Note that this is an outdated database table, and it did not have AutoField .

EDIT: Add obsolete table structure

 +------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+-------+ | userid | int(11) | NO | PRI | NULL | | | field1id | int(11) | NO | PRI | NULL | | | isfavorite | int(11) | NO | | 0 | | | isadmin | int(11) | NO | | 0 | | | role | int(11) | YES | | NULL | | | field2 | tinyint(1) | NO | | 1 | | | field3 | tinyint(1) | NO | | 1 | | | isactive | tinyint(4) | NO | | 1 | | +------------+------------+------+-----+---------+-------+ 

Answer 1

Unfortunately, django does not yet have a composite primary key (composite primary keys are also called primary keys of several columns)

There is a third-party library, unsurprisingly called django-compositekey , which allows you to create a model with multiple columns of the primary key. However, this project is now supported and incompatible with the latest versions of django.

It is best to add a new column to your table, which is AutoField, and make it the new primary key. Fields currently making up the primary key can be marked as unique_together . Although this may not be ideal for a few queries. This is good enough for most.

If you update your question with the current table structure (as shown in your sql console), I will update my answer to show how the model will look.

Updating There are many ways in which you can delete an old compound primary key and replace it with a new auto-increment primary key. The simplest here, it only includes SQL

 CREATE TABLE newtable LIKE mytable; ALTER TABLE newtable DROP PRIMARY KEY; ALTER TABLE newtable ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY; RENAME TABLE mytable to mytable_old; RENAME TABLE newtable to mytable; INSERT INTO mytable(userid,field1id, rest of the fields) SELECT * FROM mytable_old; 

Then edit your model and remove the primary_key = True flag from these fields.

Footnote:
For example, some databases, such as sqlite, do not support the LIKE in CREATE TABLE . In these cases, you will have to look for the create table statement for the source table and copy the insert after editing the table name. Judging by your table structure, you seem to be using mysql and supporting the LIKE .

Answer 2

You must remove the Remote = False command from the model in order to be able to edit the table