This is the case. I have a table that stores the exam.
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 indicates the examination type, and date indicates the examination date. A unique_together is added to table creation because only one test is required for one type of test. However, this unique_together is not required due to business needs.
Anyone who has used django knows that this is not a big problem. Delete the unique_together code and then makemigrations. Indeed, I did. However, when I use migrate, an error is reported. The error is as follows:
Django. db. utils. OperationalError: (1553, "Cannot drop index 'insurance _ exam_category_id_a1_e581_uniq': needed in a foreign key constraint ")
The database does not allow me to delete this index and tells me that it is used by a foreign key constraint. I am surprised that category is a foreign key, but this is unique_together. How can I use this foreign key?
No way, I can only search for the answer in the database, 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_a1_e581_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 |
The row of unique key is unique_together, and the row below is the foreign KEY of category. Nothing else. Which foreign key uses our unique_together?
Foreign keys can only be category, and there are no other foreign keys. What is going on?
The reason is: the foreign keys in Mysql will automatically add an index to the table, that is, if there is no unique_together, our table should be 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 |
However, because unique_together unique_key is available and category is on the left of the Union index, the category index is available based on the leftmost prefix principle, so no additional index is created, at this time, the foreign key constraint of category depends on this unique_key. Therefore, this error will be reported during deletion.
Witty friends should have thought of it. If we want to remove unique_together, we can add the KEY of category back, so that we can delete unique_together. The SQL statement is as follows:
Alter table exam add index (category_id );
In this way, migrate will be successful.