How do I add a Foreign Key to a new SQLite DB in Xojo?

G’Day guys

New to Xojo and new to SQLite.

I’m using DB Browser for SQLite (both Windows and MAC) and due to problems creating tables with that, I discovered that I could create my ‘simple’ DB directly in Xojo, using the Insert Menu.

I like this idea because it seems that the DB will be ‘embedded’ in my application and I won’t have to have a separate DB file? Is that a correct assumption?

If that’s the case, then that’s (at least) how I think I should start. My DB is simple, a couple of tables with just a couple of Foreign Keys.
I’m unsure how to create a Foreign Key using Xojo as I create the DB, Tables, and Fields. I don’t see a setting that identifies a field as a Foreign Key?

Any advice/guidance will be greatly appreciated.

Cheers, Ken.

with sqlite, you can use an in-memory database, that will be “embedded” in your application
BUT, the datas of this database will also disappear when you quit the app !!!

so if you want your datas to be stored permanently, you need to make a on-disk sqlite database.
(insert->database->new sqlite database)

and no the database editor in the xojo ide is very light, and you won’t be able to create your FK
you could use the sqlexecute function you can get int othe ide to create the FK but I don’t call that an editor.

there are some external (paid) programs for that, among : sqlmanager, sqlpro for sqlite, or navicat that will edit the FK

Thanks mate. Timely and informative answer … much appreciated.

I’ll continue to struggle with DB Browser for SQLite until I find a better alternative.

If I could stretch the friendship and ask a follow-up question …

Having opened (db.connect) the database once, should I close and re-connect as needed, ie., when I want to CRUD, or should I leave an open (global) connection for the life of my application session? If the latter, how to I ‘Dim’ a global variable of type SQLiteDatabase that can be used throughout my app? I did create a module and created a Property of Global scope and of type SQLiteDatabase, but that doesn’t seem to work when doing the first db.connect?

Sorry for the noob question…

take a look at the “eddie electronic” example in the xojo folders
all you want for a database is almost here.
a global property in a module is ok, a local application property can work too
you can open the db for the life of the app (less stress…) or open it and close it when you want.