Django/MySQL: Making non-unique field unique fails even if field values are unique

Question

I currently have this:

class Committee(models.Model):
    # ...some fields...
    committee_xml_id = models.IntegerField()

I need to make the field committee_xml_id unique, i.e. make it so:

class Committee(models.Model):
    # ...some fields...
    committee_xml_id = models.IntegerField(unique=True)

I have also tried making it so:

class Committee(models.Model):
    # ...some fields...
    committee_xml_id = models.IntegerField(unique=True, db_index=False)

Alas, the result is the same.

Having run ./manage.py makemigrations and subsequently, ./manage.py migrate, the problem is this:

django.db.utils.OperationalError: (1061, "Duplicate key name 'appname_committee_committee_xml_id_d1210032_uniq'")

At first glance, it seems like the problem is that there is already non-unique data in the table, but the problem is precisely that there isn't. There are only 45 rows in the table, and the field committee_xml_id contains only unique entries.

The following query gives no results, as expected when there are no duplicates:

SELECT
    com.committee_xml_id,
    COUNT(*)
FROM
    appname_committee AS com
GROUP BY
    com.committee_xml_id
HAVING
    COUNT(*) != 1

For rigor, here is the same query without the HAVING-condition, showing clearly that there are indeed no duplicates:

SELECT
    com.committee_xml_id,
    COUNT(*)
FROM
    appname_committee AS com
GROUP BY
    com.committee_xml_id

Result is:

# committee_xml_id, COUNT(*)
78, 1
79, 1
124, 1
125, 1
129, 1
130, 1
131, 1
132, 1
133, 1
134, 1
137, 1
139, 1
140, 1
141, 1
142, 1
147, 1
148, 1
149, 1
150, 1
151, 1
152, 1
153, 1
154, 1
160, 1
166, 1
167, 1
168, 1
169, 1
170, 1
176, 1
192, 1
193, 1
194, 1
195, 1
198, 1
199, 1
200, 1
201, 1
202, 1
203, 1
204, 1
205, 1
206, 1
207, 1
216, 1

Any help greatly appreciated.

The database has probably a complicated history (also manually managed, not only by migrations, or `--fake`, maybe some modified migration files after the initial migration.) There can be a mess in migrations that the index exists but the related migration is not recorded in "django_migrations" table. Try to backup the database and `DROP INDEX appname_committee_committee_xml_id_d1210032_uniq ON appname_committee;`, then apply the migration. Or you can look at the migration SQL content first by `python manage.py sqlmigrate app_label migration_name` and see if it is the only probable conflict... — hynekcer, Nov 09 '17 at 14:54
... e.g. not also created a field that exists yet etc. I write an answer if this way helps. It is a broad question: A) How it was possible? (requires more information) B) How it can be fixed if all data are valuable. ([my general answer](https://stackoverflow.com/a/46774336/448474)) C) How it can be fixed the easiest in development if no database exists in production yet and no valuable data in development. (many trivial answers) || Can you reproduce the problem on a new database? (probably not) — hynekcer, Nov 09 '17 at 15:08
This turned out to be some sort of a half-finished job on the database that hadn't really gone through, which was screwing everything up. Thanks for the hint, @hynekcer! — Teekin, Nov 09 '17 at 18:55
OK. Drop index is safe and simple. If more conflicts will remain then look at my general answer where I added now a link how a somewhat broken database can be fixed by a selective copy-paste from `sqlmigrate` and `migrate --fake` finally. — hynekcer, Nov 09 '17 at 20:17

score 3 · Accepted Answer · answered Nov 09 '17 at 01:51

The error isn't about the data in the table. If we were attempting to insert data that violated a unique constraint, or if we attempt to define a UNIQUE key when there are duplicates in the table, we'd be seeing a different error:

Error Code 1062: Duplicate entry ...

The 1061 error occurs when we attempt to define a new key, and that key already exists.

As a demonstration:

  create table foo2 (id int);

  0 row(s) affected

  insert into foo2 (id) values (1),(1);

  2 row(s) affected

  alter table foo2 add unique key foo2_ux1 (id);

Error Code: 1062 Duplicate entry '1' for key 'foo2_ux1'

  alter table foo2 add key foo2_ix2 (id);

  0 row(s) affected

  alter table foo2 add key foo2_ix2 (id);

Error Code: 1061 Duplicate key name 'foo2_ix2'

  alter table foo2 add UNIQUE key foo2_ix2 (id);

Error Code: 1061 Duplicate key name 'foo2_ix2'

A SHOW CREATE TABLE will shows us a key of that name does in fact already exist

  CREATE TABLE `foo2` (
  `id` INT(11) DEFAULT NULL,
  KEY `foo2_ix2` (`id`)
  ) ENGINE=InnoDB

Thank you for the elaboration, but then what would make that key already exist, when it's first being made? — Teekin, Nov 09 '17 at 18:27
I'm marking your answer as the correct one, as indeed it was a case of the darned thing already existing from a previous attempt to run the migration, which had failed. This didn't become apparent until I tried with an older copy of the database, at which point the problem resolved itself. Thanks! — Teekin, Nov 09 '17 at 18:57

Django/MySQL: Making non-unique field unique fails even if field values are unique

1 Answers1