0

I'm getting the following error whenever I attempt to save to the table in a SQLite database:

foreign key mismatch - "procedure_tbl" referencing "filename_tbl"

In models.py, these are the tables that the error is referring to:

class FilenameTbl(models.Model):
    rowid = models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='FileID', db_column='rowid')
    filename = models.TextField(blank=True, null=True)
    creation_datetime = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'filename_tbl'
        ordering = ['rowid']


class ProcedureTbl(models.Model):
    rowid = models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ProcedureID', db_column='rowid')
    ...
    filename_id = models.ForeignKey(FilenameTbl,db_column='filename_id', to_field='rowid',null=True,blank=True,on_delete=models.SET_NULL)

    class Meta:
        managed = False
        db_table = 'procedure_tbl'
        ordering = ['rowid']

Data can be read from the tables and querysets like the following return the correct data:

    queryset = FilenameTbl.objects.values(
        'rowid', 'filename',
        'proceduretbl__rowid')

Raw SQLite commands to write/update to the ProcedureTbl table function properly.

If I removed filename_id from the ProcedureTbl, then data can be saved to the table:

    queryset = ProcedureTbl.objects.get(procedure_number=10)
    queryset.reviewer_comments='can save this'
    queryset.save()
Wai
  • 33
  • 3

1 Answers1

0

This is more of a workaround.

In creation of the tables, I didn't create an alias for the ROWID and just referred to ROWID as the primary key. This worked fine for RAW SQLite commands but it seems Django doesn't handle ROWID very well.

After creating aliases, the "foreign key mismatch" error went away:

class FilenameTbl(models.Model):
    filename = models.TextField(blank=True, null=True)
    creation_datetime = models.TextField(blank=True, null=True)
    filename_number = models.AutoField(primary_key=True, db_column='filename_number')

    class Meta:
        managed = False
        db_table = 'filename_tbl'
        ordering = ['filename_number']

# new ProcedureTbl

class ProcedureTbl(models.Model):
    ...
    filename_id = models.ForeignKey(FilenameTbl,db_column='filename_id',null=True,blank=True,on_delete=models.SET_NULL)
    procedure_number = models.AutoField(primary_key=True, db_column='procedure_number')

    class Meta:
        managed = False
        db_table = 'procedure_tbl'
        ordering = ['procedure_number']

I got the idea of creating aliases after reading these articles

Why does referencing a SQLite rowid cause foreign key mismatch?

Why can't you use SQLite ROWID as a Primary key?

Thanks for everyone's time.

Wai
  • 33
  • 3