bpDXTZnY

1-- Create the original table with column c having a finite number of values (0-25)
2DROP TABLE IF EXISTS original_table;
3CREATE TABLE IF NOT EXISTS original_table (A TEXT, B TEXT, C TEXT, D TEXT, E TEXT, F TEXT, G TEXT);
4
5-- Load the original table with some data
6WITH RECURSIVE counter(cola,colb,colc,cold,cole,colf,colg) AS (
7    SELECT random() % 26 AS cola, random() % 26 AS colb,abs(random() % 26) AS colc,random() % 26 AS cold,random() % 26 AS cole,random() % 26 AS colf,random() % 26 AS colg 
8    UNION ALL 
9    SELECT random() % 26 AS cola, random() % 26 AS colb,abs(random()) % 26 AS colc,random() % 26 AS cold,random() % 26 AS cole,random() % 26 AS colf,random() % 26 AS colg 
10    FROM counter LIMIT 3000
11)
12INSERT INTO original_table SELECT * FROM counter;
13SELECT * FROM original_table ORDER BY C ASC; -- Query 1 the original original_table
14
15-- Create the secondary table by extracting values from the C column of the original table
16DROP TABLE IF EXISTS secondary_table;
17CREATE TABLE IF NOT EXISTS secondary_table (id INTEGER PRIMARY KEY, c_value TEXT);
18INSERT INTO secondary_table (c_value) SELECT DISTINCT C FROM original_table ORDER BY C ASC;
19SELECT * FROM secondary_table; -- Query 2 the new secondary table
20
21-- Add the new column as a Foreign key to reference the new secondary_table
22ALTER TABLE original_table ADD COLUMN secondary_table_reference INTEGER REFERENCES secondary_table(id);
23SELECT * FROM original_table; -- Query 3 the altered original_table but without any references
24
25-- Update the original table to apply the references to the secondary_table
26UPDATE original_table 
27    SET secondary_table_reference = (SELECT id FROM secondary_table WHERE c_value = C)
28    -- >>>>>>>>>> NOTE USE ONLY 1 OR NONE OF THE FOLLOWING 2 LINES <<<<<<<<<< 
29    , C = null; -- OPTIONAL TO CLEAR COLUMN C
30    -- , C = (SELECT id FROM secondary_table WHERE c_value = C) -- ANOTHER OPTION SET C TO REFERENCE SECONDARY TABLE
31;
32SELECT * FROM original_table; -- Query 4 the final original table i.e. with references applied (column C now not needed)
33	
34-- Create the original table with column c having a finite number of values (0-25)
35DROP TABLE IF EXISTS original_table
36> OK
37> Time: 0.94s
38
39
40CREATE TABLE IF NOT EXISTS original_table (A TEXT, B TEXT, C TEXT, D TEXT, E TEXT, F TEXT, G TEXT)
41> OK
42> Time: 0.353s
43
44
45-- Load the original table with some data
46WITH RECURSIVE counter(cola,colb,colc,cold,cole,colf,colg) AS (
47    SELECT random() % 26 AS cola, random() % 26 AS colb,abs(random() % 26) AS colc,random() % 26 AS cold,random() % 26 AS cole,random() % 26 AS colf,random() % 26 AS colg 
48    UNION ALL 
49    SELECT random() % 26 AS cola, random() % 26 AS colb,abs(random()) % 26 AS colc,random() % 26 AS cold,random() % 26 AS cole,random() % 26 AS colf,random() % 26 AS colg 
50    FROM counter LIMIT 3000
51)
52INSERT INTO original_table SELECT * FROM counter
53> Affected rows: 3000
54> Time: 0.67s
55
56
57SELECT * FROM original_table ORDER BY C ASC
58> OK
59> Time: 0.012s
60
61
62-- Query 1 the original original_table
63
64-- Create the secondary table by extracting values from the C column of the original table
65DROP TABLE IF EXISTS secondary_table
66> OK
67> Time: 0.328s
68
69
70CREATE TABLE IF NOT EXISTS secondary_table (id INTEGER PRIMARY KEY, c_value TEXT)
71> OK
72> Time: 0.317s
73
74
75INSERT INTO secondary_table (c_value) SELECT DISTINCT C FROM original_table ORDER BY C ASC
76> Affected rows: 26
77> Time: 0.24s
78
79
80SELECT * FROM secondary_table
81> OK
82> Time: 0s
83
84
85-- Query 2 the new secondary table
86
87-- Add the new column as a Foreign key to reference the new secondary_table
88ALTER TABLE original_table ADD COLUMN secondary_table_reference INTEGER REFERENCES secondary_table(id)
89> OK
90> Time: 0.31s
91
92
93SELECT * FROM original_table
94> OK
95> Time: 0.01s
96
97
98-- Query 3 the altered original_table but without any references
99
100-- Update the original table to apply the references to the secondary_table
101UPDATE original_table 
102    SET secondary_table_reference = (SELECT id FROM secondary_table WHERE c_value = C)
103    -- , C = null; -- OPTIONAL TO CLEAR COLUMN C
104    , C = (SELECT id FROM secondary_table WHERE c_value = C)
105> Affected rows: 3000
106> Time: 0.743s
107
108
109SELECT * FROM original_table
110> OK
111> Time: 0.01s
112
113
114-- Query 4 the final original table i.e. with references applied (column C now not needed)
115> not an error
116> Time: 0s