After reading this django.db.utils.OperationalError: 3780 Referencing column and referenced column are incompatible and SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key are incompatible
I have two models in Django 3.2.16 declared in different apps, it's a long time project which started in Django 2.2 and was upgraded over time.
Here are the two model classes:
from city_search.models
class Città(models.Model):
nome = models.CharField(max_length = 50, db_index = True)
def __str__(self):
return "{} - {}".format(self.nome,self.regione)
provincia = models.ForeignKey(Provincia, models.SET_NULL, blank=True,null=True)
capoluogo = models.BooleanField(default=False)
regione = models.ForeignKey(Regione, models.SET_NULL, blank=True,null=True, related_name='comuni')
slug = models.SlugField(null=True)
latlng = LocationField(null=True,map_attrs={"center": [2.149123103826298,41.39496092463892], "zoom":10})
from eventos.models instead, which I'm developing
from schedule.models import events
class Manifestazione(events.Event):
ciudad = models.ForeignKey('city_search.Città', on_delete=models.CASCADE, verbose_name='Ciudad', related_name='manifestaciones', blank=False, null=False)
The migration of the latter model fails with the following error:
django.db.utils.OperationalError: (3780, "Referencing column 'ciudad_id' and referenced column 'id' in foreign key constraint 'eventos_manifestazio_ciudad_id_74f49286_fk_city_sear' are incompatible.")
these two models declarations translate to the following MySQL tables (the second is only partially created by the faulty migration)
mysql> describe city_search_città;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| nome | varchar(50) | NO | MUL | NULL | |
| provincia_id | int | YES | MUL | NULL | |
| capoluogo | tinyint(1) | NO | | NULL | |
| regione_id | int | YES | MUL | NULL | |
| slug | varchar(50) | YES | MUL | NULL | |
| latlng | varchar(63) | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
and
mysql> describe eventos_manifestazione;
+--------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+-------+
| event_ptr_id | int | NO | PRI | NULL | |
| ciudad_id | bigint | NO | | NULL | |
+--------------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Now I absolutely understand that int and bigint are a great deal of difference. However I already tried to set DEFAULT_AUTO_FIELD = 'django.db.models.AutoField' in settings.py before making the migrations and later migrating, to no avail.
According to Django Docs on automatic primary key fields I could also try to use the AppConfig so I also edited eventos/apps.py like this and later migrated
class EventosConfig(AppConfig):
default_auto_field = 'django.db.models.AutoField'
name = 'eventos'
which didn't work either. I still get the same table schemes as above.
This is the migration that such settings generated.
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = [
('city_search', '__first__'),
('schedule', '0014_use_autofields_for_pk'),
]
operations = [
migrations.CreateModel(
name='Manifestazione',
fields=[
('event_ptr', models.OneToOneField(auto_created=True, on_delete=django.db.models.deletion.CASCADE, parent_link=True, primary_key=True, serialize=False, to='schedule.event')),
('ciudad', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='manifestaciones', to='city_search.città', verbose_name='Ciudad')),
],
bases=('schedule.event',),
),
]
As I also read about a possible collation issue I will compare the table collations below:
mysql> select column_name, COLLATION_NAME, CHARACTER_SET_NAME from information_schema.`COLUMNS` where table_name = "city_search_città";
+--------------+-------------------+--------------------+
| COLUMN_NAME | COLLATION_NAME | CHARACTER_SET_NAME |
+--------------+-------------------+--------------------+
| id | NULL | NULL |
| nome | latin1_swedish_ci | latin1 |
| provincia_id | NULL | NULL |
| capoluogo | NULL | NULL |
| regione_id | NULL | NULL |
| slug | latin1_swedish_ci | latin1 |
| latlng | latin1_swedish_ci | latin1 |
+--------------+-------------------+--------------------+
7 rows in set (0.00 sec)
and
mysql> select column_name, COLLATION_NAME, CHARACTER_SET_NAME from information_schema.`COLUMNS` where table_name = "eventos_manifestazione";
+--------------+----------------+--------------------+
| COLUMN_NAME | COLLATION_NAME | CHARACTER_SET_NAME |
+--------------+----------------+--------------------+
| event_ptr_id | NULL | NULL |
| ciudad_id | NULL | NULL |
+--------------+----------------+--------------------+
2 rows in set (0.01 sec)
My hypothesis
int and bigint difference is to blame
and I'm unable due to some bug or weird reason that the migrations
are ignoring my preference to shift to int as the automatic
primary key fields?I'm unable to complete a proper migration
It seems the error text was warning about the wrong table. It all worked fine by adding DEFAULT_AUTO_FIELD = 'django.db.models.AutoField' to settings.py and make new project-wide migrations that included some tweaks to a third app's model fields, where indeed models.AutoField was set to the id fields/columns.
This third app and the one I'm developing are not connected directly through a ForeignKey, I don't understand where the relation is, although the error turned up after I created the models I mentioned in this thread. So, honestly I'm not very sure why it worked or what these third models may have to do with this new app I'm adding to the project. I would really like to know if somebody understands more.