Search code examples
sqliteprimary-keyschemaspy

SQLite is not assigning primary keys

I have a simple SQLite database created using sqlite3.exe (code is below). When I run it through SchemaSpy, it looks like there are no primary keys in my first three tables. I don't understand what is wrong with the CREATE TABLE statements in the first three tables.

Code In:

.open test1.db
PRAGMA foreign_keys = ON;

CREATE TABLE META_E (E_ID INTEGER PRIMARY KEY NOT NULL, E_Name TEXT, Region TEXT, Date DATE);

CREATE TABLE META_D (D_ID INTEGER PRIMARY KEY NOT NULL, Citation TEXT, is_used BOOLEAN);

CREATE TABLE META_G (Completion TEXT, D_ID INTEGER NOT NULL, Location TEXT, PRIMARY KEY (D_ID), FOREIGN KEY (D_ID) REFERENCES META_D (D_ID));

CREATE TABLE P_ID_Main (Source_File TEXT, P_ID INTEGER NOT NULL, E_ID INTEGER NOT NULL, D_ID INTEGER NOT NULL, PRIMARY KEY (P_ID, E_ID, D_ID), FOREIGN KEY (E_ID) REFERENCES META_E (E_ID), FOREIGN KEY (D_ID) REFERENCES META_D (D_ID));

.dump

Dump:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE META_E (E_ID INTEGER PRIMARY KEY NOT NULL, E_Name TEXT, Region TEXT, Date DATE);
CREATE TABLE META_D (D_ID INTEGER PRIMARY KEY NOT NULL, Citation TEXT, is_used BOOLEAN);
CREATE TABLE META_G (Completion TEXT, D_ID INTEGER NOT NULL, Location TEXT, PRIMARY KEY (D_ID), FOREIGN KEY (D_ID) REFERENCES META_D (D_ID));
CREATE TABLE P_ID_Main (Source_File TEXT, P_ID INTEGER NOT NULL, E_ID INTEGER NOT NULL, D_ID INTEGER NOT NULL, PRIMARY KEY (P_ID, E_ID, D_ID), FOREIGN KEY (E_ID) REFERENCES META_E (E_ID), FOREIGN KEY (D_ID) REFERENCES META_D (D_ID));
COMMIT;
sqlite>

SchemaSpy: enter image description here enter image description here

Solution

  • I don't understand what is wrong with the CREATE TABLE statements in the first three tables.

    I regard to the indexes there is nothing wrong with the CREATE TABLE statements. Rather it appears to be a shortfall of SchemaSpy.

    SQLite isn't creating indexes as there is no need as the columns E_ID, D_ID (for both the META_D and META_G tables) are aliases of the rowid column, which could be considered as the MASTER index (see link below).

    In short the column's are in fact indexed but there is no need for SQLite to create an index as the index is built in (an exception is a special type of table a WITHOUT ROWID table).

    As such the indexes won't appear in sqlite_master, as there is no need and it appears that SchemaSpy at least the way you have it configured, doesn't fully cater for SQLite.

    You may wish to refere to ROWIDs and the INTEGER PRIMARY KEY