model - Django 3 primary key issues

Question

I have an issue with my django model, basically with the primary keys.

I use the admin interface to manage my data. I have set up a database that consists of 3 primary keys to reduce multiple entries (year, month, bnr).

But I don't get this covered in my model, as django only accept one primary key. I have tried out a few things but none where helpful for me. The Admin Interface gives me an error all the time.

1. Removing all primary keys in my model
2. Tried to add an ID Column in my model/Database but not declared as primary key in my DB
3. added unique_together

Right now my model looks like this:

class BalBalance(models.Model):
    month = models.PositiveIntegerField(default=datetime.datetime.now().month, validators=[MinValueValidator(1), MaxValueValidator(12)])
    year = models.PositiveIntegerField(default=datetime.datetime.now().year, validators=[MinValueValidator(1990), MaxValueValidator(datetime.datetime.now().year)])
    bnr = models.ForeignKey('BalBankaccount', models.DO_NOTHING, db_column='BNR')  # Field name made lowercase.
    balance = models.DecimalField(db_column='BALANCE', max_digits=10, decimal_places=2)  # Field name made lowercase.
    available_balance = models.DecimalField(db_column='AVAILABLE_BALANCE', max_digits=10, decimal_places=2)  # Field name made lowercase.
    unavailable_balance = models.DecimalField(db_column='UNAVAILABLE_BALANCE', max_digits=10, decimal_places=2)  # Field name made lowercase.
    date_checked = models.DateTimeField(db_column='DATE_CHECKED')  # Field name made lowercase.
    created_at = models.DateTimeField(db_column='CREATED_AT')  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'BAL_BALANCE'
        unique_together = (('month', 'year', 'bnr'),) 

and my admin.py looks like this

    from django.contrib import admin
    from .models import *
    
    # Register your models here.
    myModels = [BalBankaccount,BalCompany,BalIncome,BalInstitution,BalLocation]
    admin.site.register(myModels)
    
    class BalBalanceAdmin(admin.ModelAdmin):
        list_display = ('month','year','bnr','balance','available_balance','unavailable_balance')
        list_filter = ('bnr',)
        ordering = ('year','month')
        search_fields = ('bnr',)
    
    admin.site.register(BalBalance,BalBalanceAdmin)

How can i get rid of this issue? Any help is appreciated

Edit: Depending of what is in place or what I have tested, django throws different errors:

If I declare the primary_key by my own I get something like:

get() returned more than one BalBalance -- it returned 12!.

Get only allows to return 1 object but in this case it returns 12 objects, because I am not allowed to declare my 3 PK as PK in django...

If I try to deal with this error by adding an ID, django throws me an error that field ID either is missing or has an Integrity Error:

IntegrityError at /admin/balance/balbalance/add/ (1364, "Field 'ID' doesn't have a default value")

question from:https://stackoverflow.com/questions/65952441/django-3-primary-key-issues

Answer 1

If I understand the last (IntegrityError) error you get, I presume it's because you are manually adding an id field as the primary key. Don't do this, as Django will automatically create an auto-incrementing id primary key for you, for any model that you don't 'override' the primary key yourself.

So remove any 'id' or primary key field you've added, and then add your unique_together in the meta as you had it. If you makemigrations and migrate, this should work as expected, and you should be able to BalBalance.objects.get(month=x, year=y, bnr=z) and reliably get just one result. This is effectively the same as having a primary key across all 3 fields as was your original intention.

EDIT: Based on your comment, I now see the underlying issue, I hadn't noticed that you had managed=False in the Meta of the model. This means that django won't create/modify the table for you, and so the changes suggested above will not have had any affect on the structure. Presumably you have this since there is existing data in an existing table that you now wish to see (and potentially manipulate) in the Django admin interface.

So the issue boils down to:

1. Django will assume (unless you specify a separate field as primary key) that you already have an auto-incrementing id column on the table. You don't have this on your table.
2. Django doesn't support primary keys across multiple fields, which is what you DO have on your table

So the solution, annoying for you as it probably is, is to make the database match the model structure defined in python, using the structure I suggested earlier.

Therefore: don't specify a PK on the model (Django will assume it has an id column), and then manually manipulate the table in the database to add an auto-incrementing id column (and set it as the primary key, removing the PK from the other 3 fields). Next, specify the unique_together on the other 3 fields in the model, add then go and add a unique key across those fields in the DB.

Once the database reflects the model definition, your pain should be removed, and the admin should work as intended with the code you pasted in your question.

Of course, depending on whether you have other software hooked up to that database table, and how those rely on PKs ... you might have issues with those, but I can't say for sure.