CODEWITHSUNDEEP
sqlite
Jonathan Allen
Jonathan Allen

Reputation: 70337

Why would PRAGMA schema.index_list(table-name); not show primary keys?

According to the documentation, PRAGMA schema.index_list(table-name); should list all of the indexes including primary keys.

However, when I use it I only get the secondary indexes.

https://www.sqlite.org/pragma.html#pragma_index_info

What would cause the primary keys to not be listed?

CREATE TABLE Employee
(
    EmployeeKey INTEGER PRIMARY KEY,
    FirstName nvarChar(25) NOT NULL,
    MiddleName nvarChar(25) NULL,
    LastName nVarChar(25) NOT NULL,
    Title nVarChar(100) null,
    EmployeeId nvarChar(50) NOT NULL,
    ManagerKey INT NULL REferences Employee(EmployeeKey),
    OfficePhone VARCHAR(15) NULL ,
    CellPhone VARCHAR(15) NULL ,
    CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
    UpdatedDate DateTime NULL
)

CREATE UNIQUE INDEX index_name ON Employee(EmployeeId);

Upvotes: 1

Views: 242

Answers (1)

MikeT
MikeT

Reputation: 57073

EmployeeKey won't be shown as an index as it's an alias of the rowid column.

  • The rowid column being inherent for all tables that are not defined as WITHOUT ROWID tables.
  • The rowid column could be considered the MASTER index.

If you were to not specify INTEGER PRIMARY KEY as the PRIMARY KEY, e.g. you specified EmployeeKey TEXT PRIMARY KEY, then an index would be listed as SQLite is quite specific about what constitutes an alias of the rowid (e.g. EmployeeKey INT PRIMARY KEY is not an alias of the rowid, so would have an index).

You may wish to have a look at CREATE TABLE - Rowid.

Upvotes: 2

Related Questions