sqlite-foreign-keys-example is a tutorial that have a bunch of examples. The examples are in the form:
[code]–
– salespeople
CREATE TABLE salespeople (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
commission_rate REAL NOT NULL
);[/code]
Of course, taking an Android example is not the best idea when you use Xojo, but the above code can be changed to looks like:
//
// salespeople
//
db.SQLExecute("CREATE TABLE salespeople (id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, commission_rate REAL NOT NULL);")
Or, better if you want to stay in the mood:
// -----------
// salespeople
db.SQLExecute("CREATE TABLE salespeople (" +_
"id INTEGER PRIMARY KEY, " +_
"first_name TEXT NOT NULL, " +_
"last_name TEXT NOT NULL, " +_
"commission_rate REAL NOT NULL);""")
And now, the code from that web page, adapted to Xojo 2016r1 (SQLIte 3.14.1) is:
[code]Public Sub SQLite_Create_Foreign_Key()
// ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
//
// SQLite Foreign Key Examples
// By Alvin Alexander. Last updated: June 3 2016
//
// ---------- ---------- ---------- ----------
//
// Adapted to Xojo by Emile Schwarz
// (“mailto:emile.schwarz@yahoo.com”)
// 2016-09-16 17H42
//
// ---------- ----------
//
Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem(“db.sqlite”)
If Not db.CreateDatabaseFile Then
// Handle error here
MsgBox “Create a SQLite Data Base File” + EndOfLine + EndOfLine +_
"Error " + Str(db.ErrorCode) + ": " + db.ErrorMessage + “.”
Return
End If
// -----------
// salespeople
db.SQLExecute(“CREATE TABLE salespeople (” +_
"id INTEGER PRIMARY KEY, " +_
“first_name TEXT NOT NULL, " +_
“last_name TEXT NOT NULL, " +_
“commission_rate REAL NOT NULL);”””)
// ---------
// customers
db.SQLExecute(“CREATE TABLE customers (” +_
“id INTEGER PRIMARY KEY,” +_
“company_name TEXT NOT NULL,” +_
“street_address TEXT NOT NULL,” +_
“city TEXT NOT NULL,” +_
“state TEXT NOT NULL, " +_
“zip TEXT NOT NULL);””")
// ------
// orders
db.SQLExecute(“CREATE TABLE orders (” +_
“id INTEGER PRIMARY KEY,” +_
“customer_id INTEGER,” +_
“salesperson_id INTEGER,” +_
“FOREIGN KEY(customer_id) REFERENCES customers(id), " +_
“FOREIGN KEY(salesperson_id) REFERENCES salespeople(id));””")
// salespeople sample data
//
db.SQLExecute(“INSERT INTO salespeople VALUES (null, ‘Fred’, ‘Flinstone’, 10.0);”)
db.SQLExecute(“INSERT INTO salespeople VALUES (null, ‘Barney’, ‘Rubble’, 10.0);”)
// customers sample data
//
db.SQLExecute(“INSERT INTO customers VALUES (null, ‘ACME, INC.’, ‘101 Main Street’, ‘Anchorage’, ‘AK’, ‘99501’);”)
db.SQLExecute(“INSERT INTO customers VALUES (null, ‘FOOBAR’, ‘200 Foo Way’, ‘Louisville’, ‘KY’, ‘40207’);”)
// orders sample data
//
db.SQLExecute(“INSERT INTO orders VALUES (null, 1, 1);”)
db.SQLExecute(“INSERT INTO orders VALUES (null, 2, 2);”)
End Sub[/code]
Put the code above as a Method and call it from a PushButton (or simply put the code in a PushButton.
Now, the interface and the reading parts have to be done. Some polish is needed too
BTW: personally, I would wrote salespeople as sales_people; or better: title case all the Table names…