Add support for multiple-column primary keys

This is something that gets asked about a lot, so I'm going to reopen this ticket as a place to keep track of it.

Personally I don't have time to work on this, but if the LazyWeb? can work up a patch, it would be quite nice to have. From an email I wrote a while back, here are the issues (that I'm aware of) that would need to be solved to make this work:

There's three basic problems in dealing with composite primary keys in Django.

The first is that a number of APIs use "obj._meta.pk" to access the primary key field (for example, to do "pk=whatever" lookups). A composite PK implementation would need to emulate this in some way to avoid breaking everything.

Second, a number of things use (content_type_id, object_pk) tuples to refer to some object -- look at the comment framework, or the admin log API. Again, a composite PK system would need to somehow not break this.

Finally, there's the issue of admin URLs; they're of the form "/app_label/module_name/pk/"; there would need to be a way to map URLs to objects in the absence of a primary key.

It seems to me that multiple primary keys could be represented simply as tuples. The admin could function as: "/app_label/module_name/key1,key2/"

Brantley: In your URL example, what happens when the key value contains a comma?

Adrian: what if in the current setup, a primary key has a "/" in it?

The current setup works with primary keys with slashes in 'em, because the admin-site regular expression is greedy. Try it yourself -- and if there's a problem, please do let us know.

I think that might be a fringe case that could be overcome in many ways.

It also seems like the admin can't recognize any unique_together foreign key pair.

What about /app_label/module_name/key1/key2/? That way we can think to access to partial key query /app_label/module_name/key1/, like in the date based generic views.

in regards to: "/app_label/module_name/key1,key2/". though not as efficient, primary keys can be text. what happens if they contain a comma, where pk1 = "i do, sometimes" and pk2 = "value of key 2"? /app_label/module_name/i do, sometimes,value of key 2/. or would this not happen?

Seems like the details for this still need to be decided.

so why not do escaping for such keys? Add class ComplexKey?... add some str method or something like this for getting escaped representation of the key. and what's the best escape sequence for ','... please discuss that without me:)

Replying to buriy <yuri@buriy.com>:

and what's the best escape sequence for ','

A second comma?

from django.db.vapourware import urlencode_pk
assert urlencode_pk(("ab","c") == "ab,f"
assert urlencode_pk(("a,b","c") == "a,,b,f"
assert urlencode_pk(("a,,b","c") == "a,,,,b,f"
assert urlencode_pk(("i do, sometimes","value of key 2") == "i do,, sometimes,value of key 2"

...although I'm trying to think of a simple RE that would code this, and I can't think of anything that would work.

Does the issue of encoding a composite key into URL prevent from solving this ticket?

To address the URL encoding issue, may I suggest to consider comma an unsafe character when encoding parts of the composite primary key? Then an unprotected comma can serve a clear mark of part separator. According to the URI syntax at W3C web site,

http://www.w3.org/Addressing/URL/5_URI_BNF.html ,

the comma character is allowed to appear unprotected in URIs.

So encoding a composite primary key should be as simple as

pk = [url_encode(pk_part, more_unsafe=",") for pk_part in pk_list]
url = ",".join(pklist)

The major roadblock is still the issue of supporting composite primary keys in the models.

I meant ",".join(pk).

I'm currently working up some support for this -- the work will depend and need to wait on the queryset refactoring code.

The currently plan is to maintain backwards compatibility on instance._meta.pk, but it will throw an exception if accessed when there are multiple pks, and it should be deprecated. There will be a new attributes, pks on the meta class, which will return a tuple of the primary key fields.

Sorry if this isn't the right place to post this, but I'm a noob... I've only used Python and Django for a week...

Currently, you can "fake" it by declaring one of the keys to be primary in Django and adding a unique constraint to the model. You'd have to do without the auto-generated SQL that Django gives you, but if you're smart enough to know how and why to use multiple primary keys, you can probably write the schema by hand. Still, this is less than ideal.

No you really can't "fake" it... You can't even with custom sql do it.

The problem is in the save function, because when you save an object it overrides all values with the declared primary key.

An example:

class book():
    shelf  = IntegerField(primary_key=True)
    level  = IntegerField()
    index  = IntegerField()

    class Meta:
        unique_together = ('shelf', 'level', 'index')
    # don't mind the following line right now
    primary = ('shelf', 'level', 'index')

Currently you can only have one book per shelf with the current django code... (I actually use 0.96, but I don't think it has changed...)

That was the problem, now to the solution! (Note, as I said, I'm a noob, and I just hacked an afternoon and came up with this)

It needs to have a field called primary specified as shown in the above model.

import django.db.models.manipulators 
import django.db.models.manager 
from django.core import validators 
from django.core.exceptions import ObjectDoesNotExist 
from django.db.models.fields import AutoField, ImageField, FieldDoesNotExist 
from django.db.models.fields.related import OneToOneRel, ManyToOneRel 
from django.db.models.query import delete_objects 
from django.db.models.options import Options, AdminOptions 
from django.db import connection, backend, transaction, models 
from django.db.models import signals 
from django.db.models.loading import register_models, get_model 
from django.dispatch import dispatcher 
from django.utils.datastructures import SortedDict 
from django.utils.functional import curry 
from django.conf import settings 
from itertools import izip 
import types 
import sys 
import os 
 
class Model(models.Model): 
    def save(self): 
        dispatcher.send(signal=signals.pre_save, sender=self.__class__, instance=self) 
 
        non_pks = [f for f in self._meta.fields if not (f.primary_key or (f.name in self.primary))] 
        otpk = [f for f in self._meta.fields if ((not f.primary_key) and (f.name in self.primary))] 
        where_and_clause = '' 
        for f in otpk: 
            where_and_clause = where_and_clause.join(' AND %s=%s ' % \ 
                (backend.quote_name(f.attname), 
                 str(getattr(self, f.attname)))) 
        # TODO: the last value in the above % thingy is probbably unsafe... 
         
        cursor = connection.cursor() 
         
        # First, try an UPDATE. If that doesn't update anything, do an INSERT.
        pk_val = self._get_pk_val() 
        pk_set = bool(pk_val) 
        record_exists = True 
        if pk_set: 
            # Determine whether a record with the primary key already exists. 
            cursor.execute("SELECT 1 FROM %s WHERE %s=%%s %s LIMIT 1" % \ 
                (backend.quote_name(self._meta.db_table), 
                 backend.quote_name(self._meta.pk.column), 
                 where_and_clause), [pk_val]) 
            # If it does already exist, do an UPDATE. 
            if cursor.fetchone(): 
                db_values = [f.get_db_prep_save(f.pre_save(self, False)) for f in non_pks] 
                if db_values: 
                    cursor.execute("UPDATE %s SET %s WHERE %s=%%s %s" % \ 
                        (backend.quote_name(self._meta.db_table), 
                        ','.join(['%s=%%s' % backend.quote_name(f.column) for f in non_pks]),
                        backend.quote_name(self._meta.pk.column),
                        where_and_clause),
                        db_values + [pk_val])
            else:
                record_exists = False
        if not pk_set or not record_exists:
            field_names = [backend.quote_name(f.column) for f in self._meta.fields if not isinstance(f, AutoField)]
            db_values = [f.get_db_prep_save(f.pre_save(self, True)) for f in self._meta.fields if not isinstance(f, AutoField)]
            # If the PK has been manually set, respect that.
            if pk_set:
                field_names += [f.column for f in self._meta.fields if isinstance(f, AutoField)]
                db_values += [f.get_db_prep_save(f.pre_save(self, True)) for f in self._meta.fields if isinstance(f, AutoField)]
            placeholders = ['%s'] * len(field_names)
            if self._meta.order_with_respect_to:
                field_names.append(backend.quote_name('_order'))
                # TODO: This assumes the database supports subqueries.
                placeholders.append('(SELECT COUNT(*) FROM %s WHERE %s = %%s %s)' % \
                    (backend.quote_name(self._meta.db_table), backend.quote_name(self._meta.order_with_respect_to.column), where_and_clause))
                db_values.append(getattr(self, self._meta.order_with_respect_to.attname))
            if db_values:
                cursor.execute("INSERT INTO %s (%s) VALUES (%s)" % \
                    (backend.quote_name(self._meta.db_table), ','.join(field_names),
                    ','.join(placeholders)), db_values)
            else:
                # Create a new record with defaults for everything.
                cursor.execute("INSERT INTO %s (%s) VALUES (%s)" %
                    (backend.quote_name(self._meta.db_table),
                     backend.quote_name(self._meta.pk.column),
                     backend.get_pk_default_value()))
            if self._meta.has_auto_field and not pk_set:
                setattr(self, self._meta.pk.attname, backend.get_last_insert_id(cursor, self._meta.db_table, self._meta.pk.column))
        transaction.commit_unless_managed()

        # Run any post-save hooks.
        dispatcher.send(signal=signals.post_save, sender=self.__class__, instance=self)

As some will see, this is a copy and paste of the original save function, but with additional constraints in the where clause everywhere.

Just import and inherit from this class where you have multiple primary keys and everything will work.

Also you need some sql like this: (for postgres 8.2)

ALTER TABLE app_book DROP CONSTRAINT app_book_pkey;
ALTER TABLE app_book ADD CONSTRAINT app_book_pkey PRIMARY KEY ("shelf", "level", "index")

This is a fix that works now for the impatient (like me), but I would really have it work natively, without this hackish code, so keep the good work up dcramer!

Happy programming wishes Niklas Ulvinge

Thanks, exactly what i was looking for.

Chris, B. Phim Online

Replying to jacob:

In the interest of being able to use Django as an admin interface for any db schema, it really should support multiple column primary keys. Currently, you can "fake" it by declaring one of the keys to be primary in Django and adding a unique constraint to the model. You'd have to do without the auto-generated SQL that Django gives you, but if you're smart enough to know how and why to use multiple primary keys, you can probably write the schema by hand. Still, this is less than ideal. This is likely to be very difficult.

################################################################################################
# Classes for models that need a composite key. Django as of 0.96 does not support composite keys
# To have composite keys effect/workarround in your model follows the steps:
# Step1: Paste the below classes in your models.py (before your composite models)
# Step2: Extend you class from CompositeKeyModel
# Step3: Define 'unique_together=(("field1","field2","foreignkey__field"),) in your models Meta class
# Step4: additionally you can define unique index on the database
#
# Thats it! The save() on the instances of these models should have the composite key effect.
#
# Send feedback to: Ravi.Gidwani@gmail.com
##################################################################################################

class CompositeKeyModel(models.Model): pass

class IntermediateModelBase(ModelBase):
  def __new__(cls, name, bases, attrs):
    if CompositeKeyModel in bases:
      if bases == (CompositeKeyModel,):
        # create the class via the super method
        newclass = ModelBase.__new__(ModelBase, name, (models.Model,), attrs)
        # but then make it inherit from our model class
        newclass.__bases__ = (CompositeKeyModel,)
        return newclass
      else: raise Exception, "IntermediateModelBase does not support more than one base"
    else:
      return type.__new__(cls, name, bases, attrs)


class CompositeKeyModel(models.Model):
    __metaclass__ = IntermediateModelBase
    def save(self):
         filter = {}
         #contruct model filter on the fly for the fields that listed in the
         # 'unique_together' meta attribute
         for field_name_list in self._meta.unique_together:
             for field in field_name_list:
                 filter[field]='%s' % (self.getFieldValue(field))     

         #use the generated filter to check if the object already exist
         # if so fetch it
         fetched = self.__class__.objects.complex_filter(filter)
         #if fetched, then get its primary key and set it into the object instance
         #that is being saved.
         if(len(fetched) > 0):
             pk = self.getPrimaryKey()
             self.__setattr__(pk.name,fetched[0].__getattribute__(pk.name))
    
         #finally call the super class i.e Model save() method   
         models.Model.save(self)
         
    def getFieldValue(self,fieldName):
        separator = fieldName.find('__')
        if(separator > -1): 
            tokens = fieldName.partition('__')
            foriegnObj = getattr(self, tokens[0])
            return getattr(foriegnObj,tokens[2])
        else:
            return getattr(self, fieldName)

    def getPrimaryKey(self):                                
        for field in self._meta.fields:
            if (field.primary_key):
                return field
        
        raise Exception('No primary key')
         
#################################################################################################         

Neat hack and all, but we should stick to the topic on trac.

I agree, but I am sure it can be useful to someone,like me, until this bug is fixed. What say ?

Non-essential for 1.0.

I don't currently have time to reimplement this in qs-rf so if someone else wants to volunteer go for it.

I'm going to be working up a patch in the next few days, please see http://groups.google.com/group/django-developers/browse_thread/thread/4b2370a0652d9135 for discussion.

Is the intention to add support only for composite primary keys? What about composite, non-unique indices?

#5805 was closed as a duplicate of this ticket, but I think the intention there was different. If the fix to this ticket will not add non-unique, composite indexing to Django's model API, I suggest re-opening #5805.

I don't want this to go back and forth as a patch, simply due to how large the changeset is, but I've forked the "unofficial django git repo" and put my patch there: http://github.com/dcramer/django-compositepks/tree/master#

So far it allows declaration and creation of primary key models. You declare them in class Meta: primary_key = ('field', 'field', 'field').

There is no ForeignKey/Relational? handlers as of right now, but the admin is mostly working.

Milestone post-1.0 deleted

Replying to dcrosta: Second that. This appears to add primary-key support only. It would be nice to have non-unique composite-key indexing. It would be nice for things like the generic relations in the contenttypes app for instance.

Suggested syntax:

class MyModel(models.Model):
    field_a = models.IntegerField()
    field_b = models.IntegerField()

    class Meta:
        index_together = (('field_a', 'field_b'),)

Correct me if I'm wrong, but this seems to be a serious issue for those of us that are using a pre-existing database managed by a DBA that won't change the schema for the table(s) with a Composite Primary Key.

+1 on the generic multi-column field. To add to the complexity of the issue (or perhaps this is a different issue?) I have a DBA who has begun implementing multi-part foreign keys, and I would love to see these implemented as well.

Of course, the multi-part primary key is by far a higher priority for me, as I have quite a large number of tables using these, and this renders the Django admin essentially unusable.

+1

Sorry to be a rail bird (ie, I'm not skilled enough to help), but what is the rough ETA for this solution?