I'm working on a data model and I plan to split the columns that are in one table now into multiple tables as the expected update frequency of the different columns is expected to be very different. Still I'd like to have one single view that outputs the illustration of all tables being just one.
Basically I have a name as primary key, and I use a separate ID table (ID, NAME) where ID is a unique primary key to identify the corresponding rows.
I also have a creator table (ID, NAME) that maps lengthy names to an ID.
The main data is split in two tables where each has an ID column for correlating with the ID table.
To make a long story short, here is the table definition with a stupid example data set:
BEGIN TRANSACTION;
CREATE TABLE KEY_ID (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO KEY_ID VALUES(1,'Name');
CREATE TABLE CREATORS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO CREATORS VALUES(1,'Creator');
CREATE TABLE KEY_DATA (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), KEY_DATA VARCHAR(4096), CREATOR_ID INTEGER not NULL REFERENCES CREATORS(ID), CREATED DATETIME, MODIFIED DATETIME, LOCKED DATETIME);
INSERT INTO KEY_DATA VALUES(1,'K',1,0,0,0);
CREATE TABLE KEY_PROT (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), DK_DATA VARCHAR(128), KEK_DATA VARCHAR(128));
INSERT INTO KEY_PROT VALUES(1,'X','Y');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('KEY_ID',1);
CREATE VIEW V AS SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA AS DK_DATA, KEY_PROT.KEK_DATA AS KEK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT JOIN ID ON KEY_ID.ID = KEY_DATA.ID JOIN ID ON KEY_PROT.ID = KEY_DATA.ID JOIN ID ON CREATORS.ID = KEY_DATA.ID;
COMMIT;
When querying the view I get his error essage:
sqlite> select * from V;
Parse error: no such table: main.ID
What confuses me is that my view does not contain any table named main.
Maybe the while question can be re-phrased as "How to map a foreign key relationship in a SQLite view?".
After reading "SQLite INNER JOIN examples" in SQLite INNER JOIN with Examples I still wasn't successful, so I tried to get the desired result with a SELECT first, but even that failed:
sqlite> CREATE VIEW V AS SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA AS DK_DATA, KEY_PROT.KEK_DATA AS KEK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT FROM KEY_ID INNER JOIN ID ON KEY_ID.ID = KEY_DATA.ID INNER JOIN ID ON KEY_PROT.ID = KEY_DATA.ID INNER JOIN ID ON CREATORS.ID = KEY_DATA.ID;
Parse error: near "FROM": syntax error
EK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT FROM KEY_ID INNER JOIN ID ON
error here ---^
sqlite> SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA AS DK_DATA, KEY_PROT.KEK_DATA AS KEK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT INNER JOIN KEY_ID ON KEY_ID.ID = KEY_DATA.ID INNER JOIN KEY_ID ON KEY_PROT.ID = KEY_DATA.ID INNER JOIN KEY_DATA ON CREATORS.ID = KEY_DATA.CREATOR_ID;
Parse error: ambiguous column name: KEY_ID.NAME
SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIF
^--- error here
I found the essential hint in answer https://dba.stackexchange.com/a/267839/272354, meaning that there are parent tables and child tables.
Basically the error messages from SQLite aren't very helpful, and the solution is to drop most AS phrases in SELECT and to start with a FROM and the parent table (KEY_ID).
So I'm "disassembling" the view as numbered list below:
CREATE VIEW V # the view SELECT KEY_ID.NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA # the fields FROM KEY_ID # the parent table INNER JOIN KEY_DATA USING (ID) # first child table INNER JOIN KEY_PROT USING(ID) # second child table JOIN CREATORS ON CREATORS.ID = KEY_DATA.CREATOR_ID; # third child table using different names in the fields; it seems INNER JOIN CREATORS ON CREATOR_ID also worksHere is the execution plan:
sqlite> explain select * from V;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 29 0 0 Start at 29
1 OpenRead 1 2 0 2 0 root=2 iDb=0; KEY_ID
2 OpenRead 2 5 0 6 0 root=5 iDb=0; KEY_DATA
3 OpenRead 3 6 0 3 0 root=6 iDb=0; KEY_PROT
4 OpenRead 4 4 0 2 0 root=4 iDb=0; CREATORS
5 Rewind 1 28 0 0
6 Rowid 1 1 0 0 r[1]=KEY_ID.rowid
7 SeekRowid 2 27 1 0 intkey=r[1]
8 Rowid 1 2 0 0 r[2]=KEY_ID.rowid
9 Rowid 2 3 0 0 r[3]=KEY_DATA.rowid
10 Ne 3 27 2 83 if r[2]!=r[3] goto 27
11 Rowid 1 4 0 0 r[4]=KEY_ID.rowid
12 SeekRowid 3 27 4 0 intkey=r[4]
13 Rowid 1 3 0 0 r[3]=KEY_ID.rowid
14 Rowid 3 2 0 0 r[2]=KEY_PROT.rowid
15 Ne 2 27 3 83 if r[3]!=r[2] goto 27
16 Column 2 2 5 0 r[5]=KEY_DATA.CREATOR_ID
17 SeekRowid 4 27 5 0 intkey=r[5]
18 Column 1 1 6 0 r[6]=KEY_ID.NAME
19 Column 2 1 7 0 r[7]=KEY_DATA.KEY_DATA
20 Column 4 1 8 0 r[8]=CREATORS.NAME
21 Column 2 3 9 0 r[9]=KEY_DATA.CREATED
22 Column 2 4 10 0 r[10]=KEY_DATA.MODIFIED
23 Column 2 5 11 0 r[11]=KEY_DATA.LOCKED
24 Column 3 1 12 0 r[12]=KEY_PROT.DK_DATA
25 Column 3 2 13 0 r[13]=KEY_PROT.KEK_DATA
26 ResultRow 6 8 0 0 output=r[6..13]
27 Next 1 6 0 1
28 Halt 0 0 0 0
29 Transaction 0 0 5 0 1 usesStmtJournal=0
30 Goto 0 1 0 0
(I'm tempted to ask: Who can explain the output of explain?)
All together in a new example:
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE KEY_ID (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO KEY_ID VALUES(1,'Name1');
INSERT INTO KEY_ID VALUES(2,'Name2');
INSERT INTO KEY_ID VALUES(3,'Name3');
CREATE TABLE CREATORS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO CREATORS VALUES(1,'Creator1');
INSERT INTO CREATORS VALUES(3,'Creator3');
CREATE TABLE KEY_DATA (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), KEY_DATA VARCHAR(4096), CREATOR_ID INTEGER not NULL REFERENCES CREATORS(ID), CREATED DATETIME, MODIFIED DATETIME, LOCKED DATETIME);
INSERT INTO KEY_DATA VALUES(1,'K1',1,0,0,0);
INSERT INTO KEY_DATA VALUES(2,'K2',3,0,0,0);
INSERT INTO KEY_DATA VALUES(3,'K3',1,0,0,0);
CREATE TABLE KEY_PROT (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), DK_DATA VARCHAR(128), KEK_DATA VARCHAR(128));
INSERT INTO KEY_PROT VALUES(1,'A','B');
INSERT INTO KEY_PROT VALUES(2,'C','D');
INSERT INTO KEY_PROT VALUES(3,'E','F');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('KEY_ID',3);
INSERT INTO sqlite_sequence VALUES('CREATORS',3);
CREATE VIEW V AS SELECT KEY_ID.NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA FROM KEY_ID INNER JOIN KEY_DATA USING (ID) INNER JOIN KEY_PROT USING(ID) JOIN CREATORS ON CREATORS.ID = KEY_DATA.CREATOR_ID;
COMMIT;
.headers on
sqlite> select * from V;
NAME|KEY_DATA|CREATOR|CREATED|MODIFIED|LOCKED|DK_DATA|KEK_DATA
Name1|K1|Creator1|0|0|0|A|B
Name2|K2|Creator3|0|0|0|C|D
Name3|K3|Creator1|0|0|0|E|F