Django model tries to auto-create a primary key field even though it is already specified

Question

It is stated in the Django documentation that each model requires a Primary Key. In my MySQL schema it is specified that for my table the primary key is made up of multiple columns. Using inspectdb to auto-generate the model creates the unique_together Meta information.

class Connections(models.Model):
    router = models.ForeignKey('Routers', models.CASCADE)
    src_mac = models.CharField(db_column='src_MAC', max_length=17)  # Field name made lowercase.
    src_ip = models.CharField(max_length=45, blank=True, null=True)
    src_port = models.CharField(max_length=45)
    dest_mac = models.CharField(db_column='dest_MAC', max_length=17, blank=True, null=True)  # Field name made lowercase.
    dest_ip = models.CharField(max_length=45)
    dest_port = models.CharField(max_length=45)
    first_activity = models.DateTimeField(blank=True, null=True)
    last_activity = models.DateTimeField(blank=True, null=True)
    hits_counter = models.IntegerField(blank=True, null=True)

def __unicode__(self):
    return self.src_ip

class Meta:
      db_table = 'Connections'
      unique_together = (('router', 'src_mac', 'src_port', 'dest_ip', 'dest_port'),)

When I go to run

> python manage.py shell

>>> Connections.objects.all()

it gives me the OperationalError: (1054, "Unknown column 'Connections.id' in 'field list'") to my understanding this means that Django is trying to auto-create the primary key column for Connections. How do I get around this?

Answer 1

tl;dr: Recreate the table or add the "id" field to the database manually.

I don't believe it's possible to create a primary key in all databases. For example when using SQLite, if I create a simplified version of your table, altering it to add a PK returns an error:

> CREATE TABLE connections(src_MAC CHAR(17) NOT NULL, first_activity DATETIME NULL);
> INSERT INTO connections VALUES ('abcd1234',date('now'));
> ALTER TABLE connections add column id integer NOT NULL PRIMARY KEY AUTOINCREMENT;
Error: Cannot add a PRIMARY KEY column

Similarly, running a Django migration to create the field with SQLite simply fails to add the field silently (possibly a Django issue).

If you're unlucky and use a database that does not support adding a primary key (if you're not sure, check by running the ALTER command), your best bet would be to dump the table data, drop the table, create a new table with a primary key and finally reload the table data. This seems also to be the safest way to do it generally.

On the other hand if you do use a database that supports adding a primary key, you can either try to make migrations or alter the database manually.

Creating the id field manually in Postgres is as simple as:

ALTER TABLE connections ADD COLUMN id SERIAL PRIMARY KEY;

This here is sufficient to make the Model usable in Django. Now for the hard way.

---

It seems that makemigrations assumes an ID field already exists, and thus if you opt to use migrations, the initial migration would need a fix (remove the id field). You can create the CreateField migration manually thus:

migrations/0002_connections_id.py

# -*- coding: utf-8 -*-
# Generated by Django 1.9.7 on 2016-07-08 08:56
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('testapp', '0001_initial'),
    ]

    operations = [
        migrations.AddField(
            model_name='connections',
            name='id',
            field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'),
        ),
    ]

Unfortunately, this still doesn't seem to be enough, since the created column does not auto-increment (possibly another Django issue). The solution now would be to alter the created field like so:

> ALTER TABLE connections ALTER id SET default nextval('connections_id_seq');

Finally the values make sense to Django and you can now query and create "Connections" instances as usual.

Just in case anyone needs this later, I've pushed the code I used to test this to this GitHub repo.