Django postgress - multiple primary keys are not allowed error

Question

I am running migrations on my production system which uses a Postgress database and when I run it I get this error:

django.db.utils.ProgrammingError: multiple primary keys for table "website_experience" are not allowed

But works well on my development SQL database. Here's the model I'm working with:

class Experience (models.Model):
    title = models.CharField(max_length = 60)
    company = models.CharField(max_length = 60)
    city = models.CharField(max_length = 60)
    start_date = models.DateField(blank=False, default=datetime.now)
    end_date = models.DateField(blank=True, null=True)
    description = models.CharField(max_length = 1000)
    creative_user = ForeignKey(CreativeUserProfile, models.CASCADE)

Initially, the field creative_user (which is my extended User model) was a primary key, but changed it to be a ForeignKey to express One to Many relationship between One CreativeUser having Many work Experience.

Here is the migration before and after making the change to ForeignKey

class Migration(migrations.Migration):

    dependencies = [
        ('website', '0003_auto_20170510_1436'),
    ]

    operations = [
        migrations.CreateModel(
            name='Experience',
            fields=[
                ('title', models.CharField(max_length=60)),
                ('company', models.CharField(max_length=60)),
                ('city', models.CharField(max_length=60)),
                ('startDate', models.DateField()),
                ('endDate', models.DateField(blank=True, null=True)),
                ('creative_user', models.OneToOneField(on_delete=django.db.models.deletion.CASCADE, primary_key=True, serialize=False, to='website.CreativeUserProfile')),
            ],
        ),
]

This expresses the creation of Experience model and that creative_user was primary key on model. Then after making it a ForeignKey the migration looked like:

class Migration(migrations.Migration):

    dependencies = [
        ('website', '0004_experience'),
    ]

    operations = [
        migrations.AddField(
            model_name='experience',
            name='id',
            field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'),
            preserve_default=False,
        ),
        migrations.AlterField(
            model_name='experience',
            name='creative_user',
            field =models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='website.CreativeUserProfile'),
    ),
]

As I said this all works on dev but migrating on Postgress DB thinks I have multiple primary keys. Can anyone shine some light on what wrong I'm doing?

Thanks.

score 5 · Answer 1 · answered Apr 02 '19 at 15:04

Maybe is a issue related to the order of migration changes. I had this in my migration file:

operations = [
    migrations.AddField(
        model_name='unsubscriber',
        name='id',
        field=models.AutoField(default=None, primary_key=True, serialize=False),
        preserve_default=False,
    ),
    migrations.AlterField(
        model_name='unsubscriber',
        name='phone',
        field=models.IntegerField(verbose_name='Teléfono'),
    ),
]

In the example I wanted to change the primary_key from phone to the new field called id, as you can see this migration is trying to create the new field as PK without changing the old one.

Just changing the order to this must work:

operations = [

        migrations.AlterField(
            model_name='unsubscriber',
            name='phone',
            field=models.IntegerField(verbose_name='Teléfono'),
        ),
        migrations.AddField(
            model_name='unsubscriber',
            name='id',
            field=models.AutoField(default=None, primary_key=True, serialize=False),
            preserve_default=False,
        ),
    ]

It solves the problem. I hope it helps.

score 0 · Answer 2 · answered Oct 03 '17 at 10:25

I had the same issue and managed to resolve it by deleting all the migration files from the point the affected table was created, and then run makemigrations and migrate.

Your migration file "0004_experience" created a oneToOneField named "creative_user" that was set as the primary key. My guess is, Changing from a onToOne to oneToMany relationship called for creation of a new unique field (an auto increment field "id" and set it as the primary key) in the later migration, because "creative_user" was nolonger unique.

Since the latest migration depends on migrations before it, you ended up with two primary keys. Deleting these conflicting migration files will sort you out.

score 0 · Answer 3 · answered Jun 03 '20 at 15:14

0

I have deleted all the migration files except init.py and run migration commands again.

python manage.py makemigrations
python manage.py migrate appName

which solved my problem.

answered Jun 03 '20 at 15:14

Jay Dangar

3,271
1
16
35

Django postgress - multiple primary keys are not allowed error

3 Answers3