How to implement multivalued attribute in Django?

Question

I have a legacy database with the following tables:

person

person_id (PK)  |  first_name  |  last_name
        1       |  John        |  Doe
        2       |  David       |  Bentley

phonenumbers

person_id (FK,PK) |  phone_number (PK)  | area_code (PK)
        1         |  758-4551           | 909
        1         |  763-3445           | 909
        2         |  634-0011           | 637

Every person can have zero or more phone numbers, which is a multivalued attribute of the person entity.

I've tried using the Django's inspectdb command which generated the following models.py:

class Person(models.Model):
    person_id = models.BigIntegerField(primary_key=True)
    first_name = models.CharField(max_length=255)
    last_name = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = 'person'


class PhoneNumbers(models.Model):
    person = models.ForeignKey(Person, models.DO_NOTHING)
    phone_number = models.CharField(max_length=15)
    area_code = models.CharField(max_length=15)

    class Meta:
        managed = False
        db_table = 'phonenumbers'
        unique_together = (('person', 'phone_number', 'area_code'),)

However, when I tried to save a new instance of PhoneNumbers, Django returned the following error message:

django.db.utils.ProgrammingError: column phonenumbers.id does not exist

Apparently Django expects the phone number's table to have a surrogate key. Since the phone number's table is not an entity, it doesn't have a surrogate key in my legacy database. Notice that the phonenumbers' table primary key is a composition of all of its columns.

How can I map these tables into Django's models so that it works with my legacy database?

score 1 · Answer 1 · answered Jun 08 '16 at 20:13

In Django, all the Models (written for you or generated with inspectdb) must kave Primary Key.

If you like to use the table phonenumbers like a Model, you will need that this table have a primary key.

In this scenario, my advice is that you modify your legacy table to add a new primary key manually and discard your composite primary key. Your legacy table have a composite primary key (phone_number+area_code) and this is not supported officially in Django.

score 0 · Accepted Answer · answered Jun 09 '16 at 04:04

Django does not support composite primary keys and your PhoneNumbers table has a primary key that spans three columns. This ticket has been open for years. There is a third party plugin that provide composite primary key support but it's been unmaintained for two years and incompatible with the latest versions of Django.

The solution is to add the primary key. But before that do

./manage.py migrate

This will make sure that the tables needed by django are created in your legacy database.

Now modify your models to delete this line

managed = False

This signals django that alterations to the models are to be reflected in the database. Then change your model as follows.

class Person(models.Model):
    id = models.BigIntegerField(primary_key=True, db_column='person_id')
    first_name = models.CharField(max_length=255)
    last_name = models.CharField(max_length=255)

    class Meta:
        db_table = 'person'


class PhoneNumbers(models.Model):
    id = models.BigIntegerField(primary_key=True)
    person = models.ForeignKey(Person, models.DO_NOTHING)
    phone_number = models.CharField(max_length=15)
    area_code = models.CharField(max_length=15)

    class Meta:
        db_table = 'phonenumbers'
        unique_together = (('person', 'phone_number', 'area_code'),)

Then do

 ./manage.py makemigrations  your_app_name
 ./manage.py migrate

Notice that I have renamed the primary key field in Person. This is just a cosmetic change. That's because the convention is to have the primary key field as id. When you are dealing with lots of models you are likely to forget that this model's primary key was named differently. Hence the change.

How to implement multivalued attribute in Django?

2 Answers2

Linked