Django解决ForeignKey引用外键时类型不匹配问题

**

**

1.报错的代码：

File "F:pychrom_pythonwebdjangovenvmeiduo_project_venvlibsite-packagespymysqlerr.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (3780, "Referencing column 'xxx' and referenced column 'xxx' in foreign key constraint 'xxxx' are incompatible.")

2.报错的原因：

在一个模型中使用field_name=models.ForeignKey（‘models_name’）去创建一个外键字段的时候，在迁移模型的时候，django会在数据库表中创建一个field_name_id的字段，而该字段的类型与引用其他表中的主键的类型不匹配，所有在迁移的时候django会raise 3780出来.

3.查看当前迁移文件时生成的sql命令

使用终端中使用python manage.py sqlmigrate 查看当前迁移文件所产生的sql语句

当前迁移创建的外键字段类型为bigint,而我这里引用表中的主键的类型为int。

4.解决方法：

找到当前的迁移文件

class Migration(migrations.Migration):

    dependencies = [
        ('goods', '0003_rename_default_image_url_sku_default_image'),
    ]

    operations = [
        migrations.CreateModel(
            name='GoodsVisitCount',
            fields=[
                ('create_time', models.DateTimeField(auto_now_add=True, verbose_name='创建时间')),
                ('update_time', models.DateTimeField(auto_now=True, verbose_name='更新时间')),
                ('id', models.AutoField(primary_key=True, serialize=False, unique=True, verbose_name='主键')),
                ('count', models.IntegerField(default=0, verbose_name='访问量')),
                ('date', models.DateField(auto_now_add=True, verbose_name='统计日期')),
                ('category', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='goods.goodscategory', verbose_name='商品分类')),
            ],
            options={
                'verbose_name': '统计分类商品访问量',
                'verbose_name_plural': '统计分类商品访问量',
                'db_table': 'tb_goods_visit',
            },
        ),
    ]

这里是通过migrations.CreateModel方法创建表，而django通过models.ForeignKey()创建外键，默认的类型为bigint,删除当前迁移文件migrations.CreateModel()方法的代码，改为通过migrations.runsql方法的sql语句的形式创建表，如下：

class Migration(migrations.Migration):

    dependencies = [
        ('goods', '0003_rename_default_image_url_sku_default_image'),
    ]

    operations = [

        migrations.RunSQL(
            """
            CREATE TABLE tb_goods_visit(
            create_time datetime(6) NOT NULL,
            update_time datetime(6) NOT NULL, 
            id integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
            count integer NOT NULL, 
            date date NOT NULL, 
            category_id int NOT NULL,
            CONSTRAINT tb_goods_visit_category_id_b3e36237_fk_tb_goods_category_id FOREIGN KEY(category_id) REFERENCES tb_goods_category(id)
             );
            """
    )
    ]

执行python manage.py migrate 命令

(meiduo_project_venv) F:pychrom_pythonwebdjangomeiduo_projectmeiduo>python manage.py migrate
Operations to perform:
  Apply all migrations: admin, areas, auth, contents, contenttypes, goods, oauth, sessions, users
Running migrations:
  Applying goods.0004_goodsvisitcount... OK

5.测试：

>>>(meiduo_project_venv) F:pychrom_pythonwebdjangomeiduo_projectmeiduo>python manage.py shell
>>> from goods.models import GoodsVisitCount
>>> a=GoodsVisitCount.objects.get(id=1)
>>> a.category

>>> a.category.name
'相机'

成功关联表查询出记录