Am 14.08.2018 um 20:42 schrieb Bernd Lehmkuhl:
Good day altogether,
I suppose there might be a problem with reporting missing indexes on
foreign key columns in conjunction with the without rowid clause:
C:\Users\Bernd>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t_parent (id text PRIMARY KEY NOT NULL);
sqlite> CREATE TABLE t_child_standard (id text PRIMARY KEY NOT NULL
REFERENCES t_parent (id) ON DELETE CASCADE, somethingelse text);
sqlite> CREATE TABLE t_child_wr (id text PRIMARY KEY NOT NULL REFERENCES
t_parent (id) ON DELETE CASCADE, somethingelse text) WITHOUT ROWID;
sqlite> .lint fkey-indexes
CREATE INDEX 't_child_wr_id' ON 't_child_wr'('id'); --> t_parent(id)
sqlite> .q
C:\Users\Bernd>
It looks like the primary key isn't realized as such when the without
rowid clause is used.
Thanks, Bernd
Moreover, it'd be a bit more comfortable if the names of the index,
table and column of the resulting sql weren't put in single quotation
marks, but that's not so important.
But I consider it a bug - even though I know I have to be careful with
this word - because in both cases the primary keys are recognized as such:
sqlite> pragma index_list(t_child_standard);
0|sqlite_autoindex_t_child_standard_1|1|pk|0
sqlite> pragma index_list(t_child_wr);
0|sqlite_autoindex_t_child_wr_1|1|pk|0
sqlite>
Oh - just saw this in the change logs:
3.22.0
(10) Improvements to the command-line shell:
...
5. Enhance the ".lint fkey-indexes" command so that it works with
WITHOUT ROWID tables.
...
Regression? Nope. Same output in 3.22.0 ...
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
