Example SQLite session with keys and a lot of fun

SQLiteSessionExample.txt

$ sqlite3 DATOS.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .read tareas.sql
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE personas(
	id INTEGER PRIMARY KEY,
	name TEXT
);
CREATE TABLE tareas(
	id INTEGER PRIMARY KEY,
	tarea TEXT
);
CREATE TABLE rel_tareas_personas(
	id_tarea INTEGER,
	id_persona INTEGER,
	PRIMARY KEY(id_tarea, id_persona),
	FOREIGN KEY(id_tarea) REFERENCES tareas(id),
	FOREIGN KEY(id_persona) REFERENCES personas(id)
);
CREATE TABLE tareas_bloqueadas(
	id_tarea INTEGER,
	id_tarea_bloqueada INTEGER,
	PRIMARY KEY(id_tarea, id_tarea_bloqueada),
	FOREIGN KEY(id_tarea) REFERENCES tareas(id),
	FOREIGN KEY(id_tarea_bloqueada) REFERENCES tareas(id)
);
COMMIT;
sqlite> INSERT INTO personas (name) VALUES ("Ekaitz"), ("Forzudo");
sqlite> select * from personas;
1|Ekaitz
2|Forzudo
sqlite> .explain on
sqlite> .headers on
sqlite> select * from personas;
id    name         
----  -------------
1     Ekaitz       
2     Forzudo      
sqlite> INSERT INTO tareas (tarea) VALUES ("Comprar fruta"),("Levantar sofá");
sqlite> select * from tareas;
id    tarea        
----  -------------
1     Comprar fruta
2     Levantar sofá
sqlite> INSERT INTO rel_tareas_personas (id_tarea, id_persona) VALUES (1, 1), (2, 1), (2, 2);
sqlite> select * from rel_tareas_personas;
id_t  id_persona   
----  -------------
1     1            
2     1            
2     2            
sqlite> PRAGMA foreign_keys=on;
sqlite> INSERT INTO rel_tareas_personas (id_tarea, id_persona) VALUES (0, 1);
Error: FOREIGN KEY constraint failed
sqlite> INSERT INTO rel_tareas_personas (id_tarea, id_persona) VALUES (1, 0);
Error: FOREIGN KEY constraint failed
sqlite> SELECT * FROM personas WHERE name LIKE "Eka%";
id    name         
----  -------------
1     Ekaitz       
sqlite> SELECT * FROM personas LEFT JOIN rel_tareas_personas ON rel_tareas_personas.id_persona = personas.id WHERE name LIKE "Eka%";
id    name           id_t  id_p
----  -------------  ----  ----
1     Ekaitz         1     1   
1     Ekaitz         2     1   
sqlite> SELECT * FROM personas LEFT JOIN rel_tareas_personas ON rel_tareas_personas.id_persona = personas.id LEFT JOIN tareas ON rel_tareas_personas.id_tarea = tareas.id WHERE name LIKE "Eka%";
id    name           id_t  id_p  id    tarea        
----  -------------  ----  ----  ----  -------------
1     Ekaitz         1     1     1     Comprar fruta
1     Ekaitz         2     1     2     Levantar sofá
sqlite> SELECT * FROM personas LEFT JOIN rel_tareas_personas ON rel_tareas_personas.id_persona = personas.id LEFT JOIN tareas ON rel_tareas_personas.id_tarea = tareas.id WHERE name LIKE "Forzudo";
id    name           id_t  id_p  id    tarea        
----  -------------  ----  ----  ----  -------------
2     Forzudo        2     2     2     Levantar sofá
sqlite> SELECT * FROM tareas t LEFT JOIN rel_tareas_personas r ON r.id_tarea = t.id;
id    tarea          id_t  id_p
----  -------------  ----  ----
1     Comprar fruta  1     1   
2     Levantar sofá  2     1   
2     Levantar sofá  2     2   
sqlite> SELECT * FROM tareas t LEFT JOIN rel_tareas_personas r ON r.id_tarea = t.id WHERE t.tarea = "Levantar sofá";
id    tarea          id_t  id_p
----  -------------  ----  ----
2     Levantar sofá  2     1   
2     Levantar sofá  2     2   
sqlite> SELECT * FROM tareas t LEFT JOIN rel_tareas_personas r ON r.id_tarea = t.id LEFT JOIN personas p ON r.id_persona = p.id  WHERE t.tarea = "Levantar sofá";
id    tarea          id_t  id_p  id    name         
----  -------------  ----  ----  ----  -------------
2     Levantar sofá  2     1     1     Ekaitz       
2     Levantar sofá  2     2     2     Forzudo      
sqlite> SELECT * FROM tareas t LEFT JOIN rel_tareas_personas r ON r.id_tarea = t.id LEFT JOIN personas p ON r.id_persona = p.id  WHERE t.tarea = "Comprar fruta";
id    tarea          id_t  id_p  id    name         
----  -------------  ----  ----  ----  -------------
1     Comprar fruta  1     1     1     Ekaitz       
sqlite> INSERT INTO tareas (tarea) VALUES ("Limpiar bajo el sofá");
sqlite> INSERT INTO tareas (tarea) VALUES ("Mover el sofá");
sqlite> select * from tareas;
id    tarea        
----  -------------
1     Comprar fruta
2     Levantar sofá
3     Limpiar bajo el sofá
4     Mover el sofá
sqlite> INSERT INTO tareas_bloqueadas (id_tarea, id_tarea_bloqueada) VALUES (2, 3), (2, 4), (2,5);
Error: FOREIGN KEY constraint failed
sqlite> select * from tareas;
id    tarea        
----  -------------
1     Comprar fruta
2     Levantar sofá
3     Limpiar bajo el sofá
4     Mover el sofá
sqlite> select * from tareas_bloqueadas ;
sqlite> INSERT INTO tareas_bloqueadas (id_tarea, id_tarea_bloqueada) VALUES (2, 3), (2, 4);
sqlite> select * from tareas_bloqueadas ;
id_t  id_tarea_bloq
----  -------------
2     3            
2     4            
sqlite> SELECT * FROM tareas t JOIN tareas_bloqueadas b ON t.id = b.id_tarea;
id    tarea          id_t  id_t
----  -------------  ----  ----
2     Levantar sofá  2     3   
2     Levantar sofá  2     4   
sqlite> SELECT * FROM tareas t JOIN tareas_bloqueadas b ON t.id = b.id_tarea JOIN tareas t1 ON t1.id = b.id_tarea_bloqueada;
id    tarea          id_t  id_t  id    tarea        
----  -------------  ----  ----  ----  -------------
2     Levantar sofá  2     3     3     Limpiar bajo el sofá
2     Levantar sofá  2     4     4     Mover el sofá