Foreign Key in Sqlite

[jwtea]

Hello,
I'm quite new at livecode as well as sqlite. I got a database created by sqlite using livecode.
I already created 2 table and i want to insert a foreign in 1 of the table but i know that my sqlite version is 3.15.0 that why cannot insert foreign key. I checked my sqlite version using "select sqlite_version()"

The livecode version is = LiveCode Community 8.1.9

Any idea how i can update my sqlite version or how can i insert foreign key in my table?


Thanks!

[bogs]

This post on Stack Overflow sums it up pretty well. There is no SQLite you can update to that will allow adding the key after table creation, per SQLite's own website, linked in the answer.

[jwtea]

Hello bogs,

Can i know what is the specify code needed to type in my livecode??

PS: Sorry really quite new on it, thanks for the help!

[bogs]

I'm not exactly the best at instructing about or designing databases, so take the following for what it is worth.

Most sqlite should be able to be executed pretty easily when wrapped in Lc's built in functions. My take away from the post I linked to would be that for a foreign key to exist, it has to be there at the time a table is made.

You already mentioned you created two tables in a database, so I would venture to say you would just add the part about the foreign key in that statement. It would probably look something like (starting with a new database) -

Code: Select all

// the following assumes you have a valid connection id in a variable tmpConnId...
	put "CREATE TABLE " & yourTableName & yourColumnNames & "FOREIGN KEY (yourKey_id) REFERENCES yourKey(id)" into tmpSQL
	revExecuteSQL tmpConnId, tmpSQL

However, my understanding may not be complete, and as I say there are others far better at sqlite than I am.

[jwtea]

Hello bogs,

I had tried and still failed to work
Still appreciate your effort and your time!

[bogs]

Maybe someone better acquainted with Lc and sqlite will chime in

[SparkOut]

I don't know and can't test at the moment so not sure if thus is useful but the FOREIGN KEY constraint was added to SQLite in 2009 in version 3.6.19 so your version shouldn't need to be updated for the foreign keys to work.
But as the link bogs gave shows, you will need to create the tables with Foreign key references from scratch by exporting the data, dropping the table, creating anew with the foreign key constraints and reimporting the data. I will try and have a look when I can, but don't hold your breath.

[MaxV]

Foreign key is just something that point to another row in another table.
Just use a good design and you'll notice that foreign keys is nothing more of what you already do.
For example:
Table_A: ID, Name, surname
Table_B: ID, A_ID, phone, address

The column Table_B.A_ID is the foreign key you need.

[jwtea]

Hello MaxV,

I'm actually using your current method to take data from another table.
If the code works, why not right?

Thanks and appreciate your help!