Ggtcf

How do electrons receive energy when a body is heated?

How exactly does an Ethernet collision happen in the cable, since nodes use different circuits for Tx and Rx?

Does "Until when" sound natural for native speakers?

School performs periodic password audits. Is my password compromised?

What do *foreign films* mean for an American?

When a wind turbine does not produce enough electricity how does the power company compensate for the loss?

In the late 1940’s to early 1950’s what technology was available that could melt ice?

How does Ehrenfest's theorem apply to the quantum harmonic oscillator?

Source permutation

Why does cron require MTA for logging?

Can one live in the U.S. and not use a credit card?

Would an aboleth's Phantasmal Force lair action be affected by Counterspell, Dispel Magic, and/or Slow?

Possible to detect presence of nuclear bomb?

Windows Server Data Center Edition - Unlimited Virtual Machines

Do cubics always have one real root?

Are all players supposed to be able to see each others' character sheets?

Can the alpha, lambda values of a glmnet object output determine whether ridge or Lasso?

Does a difference of tense count as a difference of meaning in a minimal pair?

Proving a statement about real numbers

Is a piano played in the same way as a harmonium?

Which situations would cause a company to ground or recall a aircraft series?

Is it a Cyclops number? "Nobody" knows!

For which categories of spectra is there an explicit description of the fibrant objects via lifting properties?

Power Strip for Europe

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

0

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);

sqlite

share|improve this question

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

add a comment | 

0

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);

sqlite

share|improve this question

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

add a comment | 

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);

sqlite

share|improve this question

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

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);

share|improve this question

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

share|improve this question

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

asked Mar 7 at 4:59

Jonathan AllenJonathan Allen

39.5k60203389

1 Answer
1

active

oldest

votes

1

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.

share|improve this answer

edited Mar 7 at 5:34

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55036387%2fwhy-would-pragma-schema-index-listtable-name-not-show-primary-keys%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

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.

share|improve this answer

edited Mar 7 at 5:34

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643

add a comment | 

1

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.

share|improve this answer

edited Mar 7 at 5:34

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643

add a comment | 

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.

share|improve this answer

edited Mar 7 at 5:34

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643

share|improve this answer

edited Mar 7 at 5:34

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643

answered Mar 7 at 5:28

MikeTMikeT

17.2k112643