0

I have two tables on my Django database (SQLite), Field_Of_Research and Conference. Conference has three foreign keys associated to the primary key of Field_Of_Research. During the migrate command, I populate the database (reading from csv files), but when two conference with same foreign key value (for all three keys) are inserted, the UNIQUE constraint failed is diplayed. If I insert the confereces using the admin page, the database gives the same error. How can I solve this problem?

model.py

class Field_Of_Research (models.Model):
     for_id = models.PositiveIntegerField(primary_key=True)
     name = models.CharField(max_length=256)

def __str__(self):
    return self.name

class Conference (models.Model):
    conf_id = models.PositiveIntegerField(primary_key=True)
    title = models.CharField(max_length=256)
    primary_for = models.ForeignKey(to=Field_Of_Research, default=0, on_delete=models.SET_DEFAULT, related_name= 'primary_for')
    secondary_for = models.ForeignKey(to=Field_Of_Research, default=0, on_delete=models.SET_DEFAULT, related_name= 'secondary_for')
    third_for = models.ForeignKey(to=Field_Of_Research , default=0, on_delete=models.SET_DEFAULT, related_name= 'third_for')

def __str__(self):
    return self.title

populate.py

with open('./server/api/database/files/fields_of_research.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';')
    for row in csv_reader:
        f=Field_Of_Research.objects.get_or_create(
            for_id = row[0],
            name = row[1]
        )
    print(f'Fields of Research done.')
#Populates the conferences database
with open('./server/api/database/files/conferences.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';')
    for row in csv_reader:
        print(row)
        c=Conference.objects.get_or_create(
            conf_id = row[0],
            title = row[1],
            primary_for = Field_Of_Research.objects.get(for_id=int(row[3])),
            secondary_for = Field_Of_Research.objects.get(for_id=int(row[4])),
            third_for = Field_Of_Research.objects.get(for_id=int(row[5])),
        )
    print(f'Conferences done.')

error

  File "C:\Users\andre\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
  File "C:\Users\andre\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\sqlite3\base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
  sqlite3.IntegrityError: UNIQUE constraint failed: api_conference.third_for_id
Andrea Troianiello
  • 1
  • why you have taken 3 foreign key fields for the same model? – Meha Parekh Mar 05 '20 at 12:12
  • Hi, I think you forgot to use "defaults" : since name does not match, it tries to create (for_id, name) and for_id has a constraint on it => failure. Try to use "defaults" to circonvent. https://stackoverflow.com/questions/1941212/correct-way-to-use-get-or-create – mansuetus Mar 05 '20 at 12:15
  • @Andrea try to remove `to=` – Zaraki Kenpachi Mar 05 '20 at 12:17
  • Can you update your question to show the META of the models please as from the code you have shown there is no ```unique_together``` definition and I would like to make sure that you have not set a unique contracting on the individual foreign key – Steve Mapes Mar 05 '20 at 12:19
  • @MehaParekh A conference can has at most three fields of research. I know, this is not the best approach, so I was already thinking a many to many solution, but I wanted to solve this problem first. The models have not META, so there is no unique_together I solved this problem. The tables weren't update or create, so I removed them manually from the database and cancelled all migrations/cache files. After I used these commands: py manage.py migrate --fake MyApp zero py manage.py migrate – Andrea Troianiello Mar 06 '20 at 16:00
  • @AndreaTroianiello I think the database part is very much important to any project, so I think you need to use `manytomany` fields and change the whole structure of database because the current is not suitable for any case! – Meha Parekh Mar 11 '20 at 06:17

0 Answers0