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.