Index question 2
(1) By doug (doug9forester) on 2020-06-05 14:40:24 [link] [source]
What is the scope of an index name? Is it system-wide or table-wide? In other words, do I have to ensure unique names for the database, or just my table?
(2) By ddevienne on 2020-06-05 14:43:22 in reply to 1 [link] [source]
Easy enough to test out, no?
(3) By Keith Medcalf (kmedcalf) on 2020-06-05 15:49:55 in reply to 2 [source]
Things that get recorded in sqlite_master occupy a single namespace. That means that names assigned to tables, indexes, triggers, and views must be unique across a single namespace (per database).
Column names must be unique within their namespace (a single table).
Database aliases must be unique within their namespace (a connection).
(4) By doug (doug9forester) on 2020-06-05 17:57:40 in reply to 3 [link] [source]
Keith, that seems broken to me. It is required to specify the table name when you define an index. Therefore, the index already has an affinity with a table. The scope should be a table instead of a database.
Is there some reason why it is scoped thusly?
(5) By Keith Medcalf (kmedcalf) on 2020-06-05 18:32:26 in reply to 4 [link] [source]
The index is not scoped to a table. You do not CREATE or DROP and index within a table namespace, but within a global namespace.
That is, you create an index with
CREATE INDEX <name> ON <table> ... and drop an index with
DROP INDEX <name>.
If the index name were scoped within a the table namespace you would have to specify the table name when you dropped it, as in
DROP INDEX <name> ON <table> or
DROP INDEX <table>.<index>. But this is not the definition of the SQL Language. SQL puts the index name in the global (database) namespace. (trigger names, index names, table names, stored procedure names, view names, and type names (I might be missing a few) are all in the same database scope.