IntegrityError on loaddata restore into non-empty database with existing primary keys

Question

Restoring fixtures without flushing the target database
When using dumpdata to export a Django project's data and loaddata to restore it, the default behavior inserts fixture rows without removing existing rows in the target database. This design leads to primary-key collisions or unique-constraint violations if the destination already contains rows with the same identifiers. The fixture format also omits database-specific sequence state, so auto-increment counters are not reset, causing subsequent INSERT statements to generate conflicting IDs even after a seemingly successful load.
The goal is to understand the exact failure modes when loaddata encounters pre-existing data across supported backends (PostgreSQL, MySQL, SQLite) in Django 4.2–5.x, and whether any built-in option or transaction wrapper can make the operation idempotent without a full flush.

Does loaddata --ignore or any other flag suppress IntegrityError for duplicate primary keys while still loading non-conflicting rows?
How do sequence/auto-increment values behave after a partial fixture load that aborts mid-transaction on PostgreSQL versus MySQL?
Is there a documented pattern to verify that all expected objects exist and no orphaned sequence state remains after a restore into a non-empty database?

Tasadduq Burney · Answer

Root cause and behaviour
When loaddata is invoked, Django wraps all fixture rows in a single database transaction. If any row violates a primary‑key or unique constraint, the whole transaction rolls back and an IntegrityError is raised. No flag exists to skip conflicting rows; the --ignore option only prevents loading of the whole fixture file.
Answering the three questions

The --ignore flag does not suppress IntegrityError for duplicate primary keys. It merely prevents loading of the entire fixture file, so any partially loaded file that contains duplicate keys will still cause a rollback.
After a partial load that aborts, both PostgreSQL and MySQL leave their auto‑increment sequences at the last value used in the failed INSERT. The sequence is not reset, so subsequent inserts may attempt to reuse already‑taken identifiers unless the sequence is manually advanced.
There is no built‑in verification command. To confirm the restore you should query the maximum primary key and compare it with the sequence value (e.g., SELECT MAX(id) FROM app_model_id_seq;) and check for duplicate keys (SELECT id, COUNT(*) FROM app_model GROUP BY id HAVING COUNT(*)>1).

Steps to make the restore idempotent without a full flush:

Truncate or delete the target tables that will receive the fixture data, ensuring no existing rows with conflicting primary keys.
Run python manage.py loaddata . The command will succeed because the tables are empty.
Verify that the auto‑increment sequence matches the highest primary key: SELECT MAX(id) FROM app_model_id_seq; and compare to SELECT MAX(id) FROM app_model;. If they differ, reset the sequence with SELECT setval('app_model_id_seq', (SELECT MAX(id) FROM app_model), true);.
Optionally run the duplicate‑key check query to ensure no leftover conflicts.

If you are using a specific backend, let me know which database (PostgreSQL, MySQL, or SQLite) you are targeting so I can provide the exact sequence‑reset command for that system.

IntegrityError on loaddata restore into non-empty database with existing primary keys

Restoring fixtures without flushing the target database

1 answer

Root cause and behaviour

Answering the three questions

Your perspective belongs here.

1 question comment

Practical verification steps for restored data