django error on migration: "There is no unique constraint matching given keys for referenced table

Question

So I have seen that a lot of these kinds of questions have popped up (few answered) and none in a Django aspect that I saw. I am confused why I am getting the error, I am guessing i am missing something on my field decorator or what not in my model definition. Here are the two models... (one abbreviated). I thought I did everything right with unique and primary key set to true in the one table that the foreign key gives reference to but upon migrate I get this error:

django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "swsite_zoneentity"

Edit up dated the code ...

class ZoneEntity(models.Model):
    zone_number = models.CharField(max_length=100, primary_key=True)
    mpoly = models.PolygonField() #this should grow and shrink for the most representative one...
    objects = models.GeoManager() 
    created_at=models.DateField(auto_now_add=True)
    updated_at=models.DateField(auto_now=True)

class CesiumEntity(models.Model):
    be_number = models.CharField(max_length=100) #the number assigned to a foot print to distinguish
    #zone_id = models.CharField(max_length=100, null=True, blank=True)
    zone_id = models.ForeignKey('ZoneEntity', null=True, blank=True)

score 10 · Answer 1 · answered May 19 '20 at 14:38

10

To solve this, needed to add the unique constraint on the postgres table id myself.

psql <your-database-name>
ALTER TABLE swsite_zoneentity ADD CONSTRAINT zone_unique_id UNIQUE(id);

Like this answer

answered May 19 '20 at 14:38

Mahmoud Hanora

167
1
9

1

I had used `inspectdb` and just added one more new column. After trying so many ways, finally, this one worked. For me: `ALTER TABLE auth_user ADD CONSTRAINT user_id UNIQUE(id)` – Dinesh Roy Jun 14 '20 at 09:55

score 7 · Answer 2 · answered Jan 25 '21 at 15:03

7

This problem appears most times because you copied or created your database from a dump and somewhere the unique constraint on your primary key column(as well as other constraints got lost.

Solution:

Open your DB with pg4admin or any client,    Databases>your_database>schema>public>tables>your_table right-click   
on the table name, 
Choose Properties 
Select columns tabs 
switch primary key on your pk column
save/exit
run migration again

answered Jan 25 '21 at 15:03

Otobong Jerome

401
6
5

Had this problem when cancelled pg_restore on creating constraints stage – Sultan1991 Mar 15 '23 at 09:11

bobleujr · Accepted Answer · 2017-02-14T20:05:50.900

4

Codejoy,

When you define a primarykey, it is automatically set as unique.. So, just go by:

class ZoneEntity(models.Model):
    zone_number = models.CharField(max_length=100, primary_key=True)
    ....

class CesiumEntity(models.Model):
    ...
    zone_id = models.ForeignKey('ZoneEntity', null=True, blank=True)
    ...

This will automatically bind the PK of ZoneEntity with zone_id!

If the field you are trying to make the relation IS NOT the primary key, then you can add unique=True and to_field='foo'

 - python manage.py. makemigration
s
Migrations for 'module1':
  0002_auto_20170214_1503.py:
    - Create model CesiumEntity
    - Create model ZoneEntity
    - Add field zone_id to cesiumentity

 - python manage.py migrate
Operations to perform:
  Synchronize unmigrated apps: staticfiles, messages
  Apply all migrations: admin, contenttypes, module1, auth, sessions
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying module1.0002_auto_20170214_1503... OK

edited Feb 14 '17 at 20:05

answered Feb 14 '17 at 19:40

bobleujr

1,179
1
8
23

Okay did as you suggested (updated post) and reran makemigrations and migrate...and got the same error :( `django.db.utils.ProgrammingError: there is no unique constraint matching given keys for referenced table "swsite_zoneentity" ` – Codejoy Feb 14 '17 at 20:00
Please check my results.. I jsut removed the geometries because I don't have geodjango here.. Anyway, can you remove your db and/or delete your migrations folder?? this may be causing a mismatch – bobleujr Feb 14 '17 at 20:07
I don't wanna toast my database, can I just delete the migrations folder? I think there was more steps to it but I am drawing a blank on how to reset this. – Codejoy Feb 14 '17 at 20:12
take a look http://stackoverflow.com/questions/26283159/django-1-7-migrations-how-do-i-clear-all-migrations-and-start-over-from-scrat delete the migrations can be tricky sometimes.. – bobleujr Feb 14 '17 at 20:15
this one is a very good one too https://simpleisbetterthancomplex.com/tutorial/2016/07/26/how-to-reset-migrations.html – bobleujr Feb 14 '17 at 20:16
Okay tried that second link, got it to show the right no migrations necessary and faked an initial migration (because that complained my table was already there). Goodness this is my bane, I reran and got everything but my database (using pgAdmin) shows no change to the database schema. – Codejoy Feb 14 '17 at 20:45
did you get to the solution? I saw you had posted a chat here, let me know if you want some help! – bobleujr Feb 18 '17 at 17:50
1

Ty yes, I tried not to blow away the database and start new but in the end it is what I did and it all worked *Shrug* thanks for the help! – Codejoy Feb 21 '17 at 15:32

score 0 · Answer 4 · answered Aug 31 '22 at 11:03

0

I too had same issue while migrating DB from SQLite to PostgreSQL 14.4, even when referenced Foreign key had primary_key=True set.

Deleting the old migrations, solved my issue.

answered Aug 31 '22 at 11:03

Aashutosh Kumar

615
9
13

django error on migration: "There is no unique constraint matching given keys for referenced table

4 Answers4

Linked