Incompatible foreign key when attempting to link tables in Django?

Hi Django Developer,

I’m trying to make a couple basic models , and link them with foreign Key. See the below code:

class CustomerInfo(models.Model):
    customer_name = models.CharField(max_length=255)
    id_no = models.CharField(max_length=255)
    mobile_no = models.CharField(max_length=255)
    email = models.CharField(max_length=255)
    status = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'customer_info'
class User(AbstractBaseUser):
    username = models.CharField(max_length=255, unique=True)
    user_fullname = models.CharField(max_length=500)
    role_id = models.ForeignKey(
        'Roles', on_delete=models.DO_NOTHING, db_column='role_id')
    #dept_id = models.CharField(max_length=500)
    is_head = models.IntegerField(blank=True, null=True)

I migrated this to the DB first. That worked fine, Django successfully created the new table in the database. And then I tried to make the reference via foreign key.

class User(AbstractBaseUser):
  
....
    customer_id = models.ForeignKey( 
        'CustomerInfo' , on_delete=models.DO_NOTHING, 
        db_column='customer_id' ,  blank=True, null=True)

but this doesn’t work. I get the following error:

django.db.utils.OperationalError: (3780, "Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'TS_user_customer_id_545183b3_fk_customer_info_id' are incompatible.")

I cannot figure out why this is happening because the referencing tool is successfully referencing multiple other tools, but it will not link to this one. What is the reason for this error? And how can I fix it so that I can link the tables? Why are they incompatible?

Try to change customer_id db_column into 'user_customerinfo'

Did you previously have this customer_id column and are now changing it to a ForeignKey? (Otherwise, why are you specifying the db_column name?)

I tried but it seemed doesn’t work. I got the same error.

Also, you show CustomerInfo as managed = False. What is the primary key column of the CustomerInfo model?

Yes, I had this field customer_id in the other models.

Now I change db_column name

user_customerinfo = models.ForeignKey( 
        'CustomerInfo' , on_delete=models.DO_NOTHING, 
        db_column='user_customerinfo' ,  blank=True, null=True)

I followed @AhmdMWddh and tried it. But I have the same problem.

What column is the primary key of CustomerInfo?

(Or, more accurately since you don’t specify it in your model, what column is the primary key of the customer_info table in the database?)

foreign#4

What version of Django are you using?

the version of Django is 4.0.3

I think the first thing I’d try would be to simplify the definition to see if there’s some sort of conflict among the options you’ve select.

I would do the following to try and get this straightened out:

  • Remove the customer_id column from the model

  • makemigrations / migrate

  • Check the database to see if the customer_id column exists in the User table.

    • If that column exists, drop it
  • Add the following definition to the User model:
    customer = models.ForeignKey('CustomerInfo', on_delete=models.PROTECT, null=True)

  • makemigrations / migrate

1 Like

This will not work because there is no customerinfo field on User table.

Since you are specifying foreign key relation to CustomerInfo table, the available db_columns are id and id_no and not customer_id

I think you’re misunderstanding the function of the db_column parameter. It’s a reference to the column name in the User table, not the column name being referenced in the CustomerInfo model.

Yes, looks like. If the user model is changed after the first migration of the app (0001_initial), manual fixing of schemas is required since foreign keys and many-to-many relationships are affected.

Outline of the steps required are given here Changing to a custom user model mid-project

1 Like

That’s only true if you’re changing to a different Model. Making a change to the currently-used User model is ok.

(In other words, if you’re using the default User model and then switching to a user-defined User model you would need to follow those steps. But if you are starting with a user-defined User model, you can make changes to it as desired without taking extra steps.)

But that’s a different situation than the issue here.

For some yet-to-be-identified reason, Django migrations is not matching the data type of the related field.

My guess is that this is because the target table is managed = False.

It may be necessary to edit the migration file for this User model to force the right data type.

1 Like

Thank you so much for your time; I really appreciate it.

I figured out what was going on:
The column of customer_id type is BIGINT and the references id (customerInfo) type is INT

So I fixed that by changing one of the column types to match the other will allow you to create the table with the foreign key constraint.