django model solution that removes unique_together error reporting

  • 2020-05-17 05:42:20
  • OfStack

The thing is, I have a table that stores the test


class Exam(models.Model):
 category = cached_fields.ForeignKeyField(Category)
 name = models.CharField(max_length=128)
 date = models.DateField()
 created_at = models.DateTimeField(auto_now_add=True)
 updated_at = models.DateTimeField(auto_now=True)

 class Meta:
 unique_together = ('category', 'date')

category means the type of exam and date means the date of the exam. Considering that there should only be 1 exam for each type of exam in the same table, we added 1 exam unique_together . But because of business needs, this unique_together No need.

As anyone who's used django knows, this isn't a big deal. Get rid of it unique_together The code, then makemigrations Well, yes, I did. But when I migrate The error was as follows:

django.db.utils.OperationalError: (1553, "Cannot drop index 'insurance_exam_category_id_a430e581_uniq': needed in a foreign key constraint")

The database won't let me delete this index, and it tells me that there's a foreign key constraint that USES it. I wonder, category It's a foreign key, but this one is unique_together Ah, how could any foreign key use it?

I had no choice but to search the database for the answer. show create table exam , the output is as follows:


| insurance_exam | CREATE TABLE `insurance_exam` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(128) NOT NULL,
 `date` date NOT NULL,
 `created_at` datetime(6) NOT NULL,
 `updated_at` datetime(6) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `insurance_exam_category_id_a430e581_uniq` (`category_id`,`date`),
 CONSTRAINT `insurance_exam_category_id_a2238260_fk_insurance_category_id` FOREIGN KEY (`category_id`) REFERENCES `insurance_category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1062 DEFAULT CHARSET=utf8mb4 |

You can see UNIQUE KEY So that's 1 row unique_together The next row is unique_together 0 Foreign keys. There's nothing else. Which foreign key used ours unique_together ?

Foreign keys can only be unique_together 0 There are no other foreign keys. What's going on?

The reason is that the Mysql keys automatically add an index to the table, which means that without unique_together, our table should look like this:


| insurance_exam | CREATE TABLE `insurance_exam` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(128) NOT NULL,
 `date` date NOT NULL,
 `created_at` datetime(6) NOT NULL,
 `updated_at` datetime(6) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `insurance_exam_category_id_a2238260_fk_insurance_category_id` FOREIGN KEY (`category_id`) REFERENCES `insurance_category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1062 DEFAULT CHARSET=utf8mb4 |

But because of it unique_together the unique_key , and category is to the left of the joint index. According to the leftmost prefix principle, the index of category will be available, so no other index will be built. At this time, the foreign key constraint of category relies on this unique_key, so there will be such error when deleting.

If we're going to get rid of it, we should think of it unique_together So, we can take unique_together 0 the KEY I'm going to add it back, so I can add it back unique_together Deleted. sql is as follows:


alter table exam add index(category_id);

In this way, migrate will be successful.


Related articles: