Foreign Key in Sqlite

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

Post Reply
jwtea
Posts: 59
Joined: Fri Mar 23, 2018 2:01 am

Foreign Key in Sqlite

Post by jwtea » Mon Mar 26, 2018 4:41 pm

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
Posts: 2086
Joined: Sat Feb 25, 2017 10:45 pm

Re: Foreign Key in Sqlite

Post by bogs » Mon Mar 26, 2018 4:53 pm

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.
Last edited by bogs on Tue Mar 27, 2018 2:47 am, edited 1 time in total.
Image

jwtea
Posts: 59
Joined: Fri Mar 23, 2018 2:01 am

Re: Foreign Key in Sqlite

Post by jwtea » Tue Mar 27, 2018 2:03 am

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
Posts: 2086
Joined: Sat Feb 25, 2017 10:45 pm

Re: Foreign Key in Sqlite

Post by bogs » Tue Mar 27, 2018 3:21 am

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.
Image

jwtea
Posts: 59
Joined: Fri Mar 23, 2018 2:01 am

Re: Foreign Key in Sqlite

Post by jwtea » Tue Mar 27, 2018 3:45 am

Hello bogs,

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

bogs
Posts: 2086
Joined: Sat Feb 25, 2017 10:45 pm

Re: Foreign Key in Sqlite

Post by bogs » Tue Mar 27, 2018 4:08 am

Maybe someone better acquainted with Lc and sqlite will chime in :)
Image

SparkOut
Posts: 1765
Joined: Sun Sep 23, 2007 4:58 pm

Re: Foreign Key in Sqlite

Post by SparkOut » Tue Mar 27, 2018 7:38 am

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
Posts: 1535
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Foreign Key in Sqlite

Post by MaxV » Tue Mar 27, 2018 3:20 pm

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.
Livecode Wiki: http://livecode.wikia.com
My blog: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w

jwtea
Posts: 59
Joined: Fri Mar 23, 2018 2:01 am

Re: Foreign Key in Sqlite

Post by jwtea » Wed Mar 28, 2018 3:18 am

Hello MaxV,

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

Thanks and appreciate your help!

Post Reply

Return to “Talking LiveCode”