理解/mySQL 又名欺骗 Django 中的 ForeignKey 关系

2022-01-20 00:00:00 django django-models foreign-key-relationship mysql database-design

所以我继承了一些 django.

So I've inherited some django.

mySQL 表很简单，其中 parent 不是 FK 关系，只是Parent"id:

The mySQL table is simple enough where parent is NOT a FK relationship just the "Parent" id:

CREATE TABLE `Child` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24;

但后来发起人这样做了..

But then the originator did this..

class Child(models.Model):
    """Project Child information"""
    id = models.AutoField(primary_key=True)
    parent = models.ForeignKey(Parent)
    name = models.CharField(max_length=255)

    class Meta:
        managed = False

诚然，我不是 SQL 骑师，但我知道真正的"外键关系类似于此通知 CONSTRAINT...

Admittedly I am NOT a SQL Jockey but I know that a "real" Foreign Key Relationship looks similar to this notice CONSTRAINT...

CREATE TABLE `Child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `child_63f17a16` (`parent_id`),
  CONSTRAINT `parent_id_refs_id_34923e1e` FOREIGN KEY (`parent_id`) REFERENCES `Parent` (`id`)
) ENGINE=InnoDB;

我想知道的是:

  1. 通过这种诡计"我会看到什么问题.
  2. 虽然这似乎可行 - 是否推荐或建议.
  3. 是否建议我们将 SQL 修改为 添加约束?

非常感谢！

推荐答案

  1. 没有实际约束可能会导致引用损坏、父级无效和其他类型的数据不一致.我不是 Django 专家，但我敢猜测，在大多数情况下，除非您故意添加一些无效记录，否则 Django 仍然可以很好地处理关系.

  1. Not having an actual constraint might lead to broken references, invalid parents and other sorts of data inconsistencies. I am not a Django expert but I would venture a guess that in most cases Django will still handle the relations fine unless you purposefully add some invalid records.

通常，如果您的 RDBMS 支持外键约束，则绝对没有理由不使用它们，忽略它们可能会被视为设计缺陷.

Normally, if your RDBMS supports foreign key constraints, there is absolutely no reason not to use them, and it could potentially be considered a design flaw to ignore them.

您应该考虑添加键约束.它们不仅可以让您的 DBMS 很好地了解如何优化查询，还可以确保数据的一致性.我很确定 Django 在某处有一个设置，它会在您运行 manage.py syncdb

You should consider adding the key constraints. Not only do they give your DBMS a good idea of how to optimize the queries, they also ensure consistency in your data. I am pretty sure Django has a setting somewhere that will automatically generate the SQL to add the key constraints when you run manage.py syncdb

关于为什么你应该更喜欢外键的更多信息，你应该阅读 MySQL 外键文档

For more information about why you should prefer foreign keys, you should read the MySQL Foreign Key Documentation

最有趣的是:

InnoDB 需要外键和引用键上的索引，以便外键检查可以快速且不需要表扫描.在引用表中，必须有一个索引，其中外键列按相同顺序列为第一列.如果引用表不存在，则会在引用表上自动创建此类索引.(这与一些旧版本形成对比，在这些旧版本中，索引必须显式创建，否则外键约束的创建将失败.) index_name，如果给定，则如前所述使用.

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

相关文章