fork download
copy 
  1. PRAGMA foreign_keys = ON;
  2. DROP TABLE IF EXISTS t5;
  3. DROP TABLE IF EXISTS t4;
  4. DROP TABLE IF EXISTS t3;
  5. DROP TABLE IF EXISTS t2;
  6. DROP TABLE IF EXISTS t1;
  7.  
  8. CREATE TABLE t1 (
  9.     key TEXT PRIMARY KEY NOT NULL,
  10. 	name TEXT NOT NULL UNIQUE
  11. );
  12. INSERT INTO t1 (key, name) VALUES ('t1_key_1', 't1_name_1');
  13. INSERT INTO t1 (key, name) VALUES ('t1_key_2', 't1_name_2');
  14.  
  15. CREATE TABLE t2 (
  16. 	no INTEGER PRIMARY KEY AUTOINCREMENT, 
  17. 	t1_key1 TEXT NOT NULL,
  18. 	t1_key2 TEXT NOT NULL,
  19. 	date TEXT NOT NULL,
  20. 	rate REAL NOT NULL,
  21. 	FOREIGN KEY(t1_key1) REFERENCES t1(key) ON DELETE CASCADE,
  22. 	FOREIGN KEY(t1_key2) REFERENCES t1(key) ON DELETE CASCADE
  23. );
  24. INSERT INTO t2 (t1_key1, t1_key2, date, rate) VALUES ('t1_key_1', 't1_key_1', '2012-01-01', 100);
  25. INSERT INTO t2 (t1_key1, t1_key2, date, rate) VALUES ('t1_key_1', 't1_key_1', '2012-01-02', 200);
  26. INSERT INTO t2 (t1_key1, t1_key2, date, rate) VALUES ('t1_key_1', 't1_key_2', '2012-01-04', 300);
  27.  
  28. CREATE TABLE t3 (
  29. 	key TEXT PRIMARY KEY NOT NULL,
  30. 	t1_key TEXT NOT NULL,
  31. 	FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE
  32. );
  33. INSERT INTO t3 (key, t1_key) VALUES ('t3_key_1', 't1_key_1');
  34. INSERT INTO t3 (key, t1_key) VALUES ('t3_key_2', 't1_key_2');
  35.  
  36. CREATE TABLE t4 (
  37. 	key TEXT PRIMARY KEY NOT NULL,
  38. 	name TEXT NOT NULL UNIQUE
  39. );
  40. INSERT INTO t4 (key, name) VALUES ('t4_key_1', 't4_name_1');
  41. INSERT INTO t4 (key, name) VALUES ('t4_key_2', 't4_name_2');
  42.  
  43. CREATE TABLE t5 (
  44. 	no INTEGER PRIMARY KEY AUTOINCREMENT, 
  45. 	t3_key TEXT NOT NULL,
  46. 	t4_key TEXT NOT NULL,
  47. 	date TEXT NOT NULL,
  48. 	profit REAL NOT NULL,
  49. 	FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE,
  50. 	FOREIGN KEY(t4_key) REFERENCES t4(key) ON DELETE CASCADE
  51. );
  52. INSERT INTO t5 (t3_key, t4_key, date, profit) VALUES ('t3_key_1', 't4_key_1', '2012-01-01 00:00:01', 1000);
  53. INSERT INTO t5 (t3_key, t4_key, date, profit) VALUES ('t3_key_1', 't4_key_1', '2012-01-03 00:00:02', 2000);
  54. INSERT INTO t5 (t3_key, t4_key, date, profit) VALUES ('t3_key_2', 't4_key_2', '2012-01-04 00:00:03', 3000);
  55. INSERT INTO t5 (t3_key, t4_key, date, profit) VALUES ('t3_key_2', 't4_key_2', '2012-02-24 00:00:04', 4000);
  56. INSERT INTO t5 (t3_key, t4_key, date, profit) VALUES ('t3_key_2', 't4_key_1', '2011-01-24 00:00:05', 4000);
  57.  
Success #stdin #stdout 0s 3008KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
https://ideone.com/7FPtHL
language:
SQLite (sqlite 3.27.2)
created:
11 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!