Implementing separate incrementing primary keys in Django

Question

I am developing an internal app on the side for the company I work for, and am wanting to use Django in order to learn it and Python in general, however I've hit a bit of a snag with the PKs.

I'm trying to emulate a part of the current application where 2 MySQL tables, TaskCategory and Tasks, handle the tasks that need doing. Every Task belongs to a TaskCategory, and each item in TaskCategory has its own separate incrementing number. Thus, if "Office" has 15 tasks, adding another task to it will make its Task.taskid = 16, but adding a task to "Vehicles" will make the Tasks.taskid = 51, not 17. This then gets used as a tracking number, eg the Vehicles-51 task.

This separate incrementation was achieved by having a compound primary key in Tasks consisting of taskcategoryid and taskid (which auto-increments). Tasks.taskcategoryid is not a FK, by the way (I don't think it can be anyway, as it's part of the compound PK).

As Django doesn't like more than 1 PK column I've been having some difficulty in replicating this feature. I've tried a unique_together of taskid (auto-incrementing PK) and taskcategoryid (FK) and I've tried having 2 auto-incrementing columns (id and taskid) but you can't have more than one auto-incrementing column.

Is it possible to achieve this incrementing feature with Django? I would prefer to do it without having to hack the source code but will do it if need be.

Answer 1

What is your goal here?

If you're trying to maintain field-to-field table compatibility with the older application, you're going to have problems because (as you've observed) Django does not do compound/composite keys -- it really prefers a surrogate key. Given the operations that Django permits, and current thinking about 'correct' database design, surrogate keys make sense.

If all you're really trying to do is keep most of the queries the same (or mostly similar), then you could leave the existing natural / composite key fields alone, but drop the old primary key constraint, add a new surrogate key for Django, and then add Meta data to your models to declare that the fields that make up the old composite keys are unique_together. Django will interpret that and add the appropriate unique indexes. You'll need to add some code to object creation to handle incrementing/assigning the TaskCategory ids, but the end result is that all of your old relationships are enforced by Django and the database.

As an example, here's part of the model from an application I put together to track players in a board game tournament:

class Player(models.Model):
    badge = models.IntegerField()
    name  = models.CharField( max_length = 99 )

class Round(models.Model):
    name  = models.CharField( max_length = 9 )
    number = models.IntegerField()

class Table(models.Model):
    round = models.ForeignKey( Round )
    number = models.IntegerField( 'table number')

    class Meta:
        unique_together = ( 'round', 'number' )

class Seat(models.Model):
    table = models.ForeignKey( Table )
    position = models.IntegerField( 'seat number' )
    player = models.ForeignKey( Player )

    class Meta:
        unique_together = ( 'table', 'seat' )

In this case, I'm using unique_together to ensure that while table numbers are reused for different rounds, and that seat positions are the same across tables, you will never encounter the same combination of round, table, and seat more than once.