sqlite-utils index-foreign-keys fails due to pre-existing index #335
Description
While running the command:
sqlite-utils index-foreign-keys $SQLITE_DIR/pudl.sqliteI got the following error:
Traceback (most recent call last):
File "/home/zane/miniconda3/envs/pudl-dev/bin/sqlite-utils", line 8, in <module>
sys.exit(cli())
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/click/core.py", line 829, in __call__
return self.main(*args, **kwargs)
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/sqlite_utils/cli.py", line 454, in index_foreign_keys
db.index_foreign_keys()
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/sqlite_utils/db.py", line 902, in index_foreign_keys
table.create_index([fk.column])
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/sqlite_utils/db.py", line 1563, in create_index
self.db.execute(sql)
File "/home/zane/miniconda3/envs/pudl-dev/lib/python3.9/site-packages/sqlite_utils/db.py", line 421, in execute
return self.conn.execute(sql)
sqlite3.OperationalError: index idx_generators_eia860_report_date already exists
This DB was created with the foreign key constraint PRAGMA enabled and a bunch of column-level CHECK constraints. Is this an expected behavior? Should one not try to index foreign keys if FK constraints are already being enforced within the DB?
I'm also noticing that the size of the DB after FK indexes have been added went from 483MB to 835MB, which seems like a much bigger jump than when I've done this previously.
Software versions...
- sqlite-utils 3.17.1
- sqlite 3.36.0
- SQLAlchemy 1.4.26 (used to create the DB)