many to many relationship add objects

Using Django Using the ORM
1 
class ClientAccessAccount(models.Model):
    client = models.OneToOneField(Client, models.DO_NOTHING, primary_key=True)
    account = models.ForeignKey(Account, models.DO_NOTHING)
    recent_access_date = models.DateField(blank=True, null=True)

    def __str__(self) -> str:
        return self.account.account_id

    class Meta:
        managed = False
        db_table = 'client_access_account'
        constraints = [
            models.UniqueConstraint(
                fields=['client', 'account'], name='unique_client_account'
            )
        ]

it’s model generated by inspectdb from existing MySQL database.

The underlying relationship is that one client can access many accounts and one account can be accessed by many clients. So I think it’s a many to many relationship and the SQL tables is the following code.

create table client_access_account
(
   client_id            varchar(18) not null,
   account_id           varchar(10) not null,
   recent_access_date   date,
   primary key (client_id, account_id)
);

I use py manage.py inspectdb to get the code above and modify the meta constraints from unique_together to UniqueConstraint after seeing python - How can I set two primary key fields for my models in Django? - Stack Overflow

My Question is that when I use ClientAccessAccount.objects.create(client=client, account=account, ...) to create the record that client A access another account C when client accessed account B before, I get Duplicate Entry error, saying that client A already has an account.

when use full_clean to validate the model, I also get this error.

How to solve the problem?

2

What is the “source” of this existing database? Is it a live system that is going to continue being used? Or are you replacing that system with the new one you’re building? Or are you migrating data from an old system to a new one?

You’re going to have a problem using this table in Django. Django requires a table have a primary key and Django does not support composite primary keys.

Also, client should be a ForeignKey to Client, not a OneToOneField.

This still leaves you with the need to identify a field as a primary key - but if you’re going to update this table, you’ll still have a problem.

If you don’t need access to the recent_access_date field, you can use this implicitly as the manytomany join table by defining a manytomany field in either Client or Account.

If you are going to need to access this table directly, you’re probably going to need to alter it.

If you can’t alter the table (because it’s still being used in a live system), then you’re going to to have a real problem trying to use Django with this along with the existing system.

3

It’s just lab for my course.

I think client should be the foreign key in the class ClientAccessAccount, too. But the model was generated by command py manage.py inspectdb and the MySQL table was generated by SAP PowerDesigner.

the SQL source is as follows.

create table client_access_account
(
   client_id            varchar(18) not null,
   account_id           varchar(10) not null,
   recent_access_date   date,
   primary key (client_id, account_id)
);
create table client
(
   client_id            varchar(18) not null,
   name                 varchar(10),
   home_address         longtext,
   phone_num            varchar(11),
   primary key (client_id)
);
create table account
(
   account_id           varchar(10) not null,
   branch_name          varchar(10),
   balance              float(8,2),
   account_create_date  date,
   primary key (account_id)
);
alter table client_access_account add constraint FK_client_access_account foreign key (account_id)
      references account (account_id) on delete restrict on update restrict;

I don’t know whether it’s the problem of inspectdb command or the problem of my design.

Until now, I have not met problems using the following code.

I’ve tried

def create_account_and_access(form, client: Client):
    print(form.get_branch_name())
    account_dict = create_account_dict(form)
    account = Account.objects.create(**account_dict)
    access_dict = create_access_dict(account, client, account_dict['account_create_date'])
    ClientAccessAccount.objects.create(**access_dict)
    return account


def create_access_dict(account, client, date):
    access_dict = {
        'account': account,
        'client': client,
        'recent_access_date': date,
    }
    return access_dict

to create an account and create an entry for the arg client to access the local variable account in the table ClientAccessAccount. And no exception occurs.

I’ve also tried the following code to update the recent_access_date field. And there is no exception.

    def generate(self, form: SavingAccountForm, saving_account: SavingAccount, account: Account, access: ClientAccessAccount):
        saving_account.currency_type = form.get_currency_type()
        account.balance = form.get_balance()
        saving_account.save()
        account.save()
        access.recent_access_date = timezone.now()
        access.save(update_fields=['recent_access_date'])

But when I change the code

access.save(update_fields=['recent_access_date'])

to

access.save()

Duplicate Entry exception occurs when I tried to update recent_access_date

So, is it ok not to use full_clean?

If I change the OneToOneField to ForeignKey, how can I specify the primary key?

So difficult!

4

It’s so strange that Django only allows one primary key in a model as so many tables in database have primary key on many fields.

many tables generated by many-to-many relationship have this pattern in Django using inspectdb command

class ClientOwnAccount(models.Model):
    account = models.OneToOneField(Account, models.DO_NOTHING, primary_key=True)
    client = models.ForeignKey(Client, models.DO_NOTHING)

    def __str__(self) -> str:
        return self.account

    class Meta:
        managed = False
        db_table = 'client_own_account'
        unique_together = (('account', 'client'),)
5

Inspectdb is a starting point, not the final result. You almost always need to modify what inspectdb provides.

You will need to alter the table as described (change the one to one field to be a foreign key) and add an autoincrement field for a primary key.

1 Like
6

The not allowing of a composite key is a long-identified issue. Multiple people have tried to resolve it, but there are apparently some very hard-to-solve issues with doing so.

I’ve just learned to live with it.

7

For clarity, it’s not that there are multiple primary keys, there is always only one primary key. However, databases allow for multiple fields to be joined to become that primary key.

8

Great Thanks for your reply. I’ve learned a lot and I will try the solution later as it currently works. Deadline is coming :sob:

9

HI, I’m also facing an issue where we designed the mysql db using Microsoft SQL server studio and generated models using inspect.db.I can see it works and I can see models for all tables but I wanted to generate a model diagram using graphviz for my models so I can compare it with the Microsoft SQL server db diagram and see if all relationships are fine But
I canät see a lot of my tables in the model graph generated by Graphviz.I sense maybe because of table relationship and Also when i run “python manage.py dbshell” and run simple SQL query it never returns anything .Please suggest me possible solution

10

You can’t just assume that inspectdb sees everything correctly. You will need to manually inspect your current schema to ensure the models are correct, and make the necessary adjustments.