Create SQLite Tables

[SChamblee]

LiveCode 7.0.6

I can create 1 table in a SQLite Database but I can't seem to get LC to create a second table in the same databases.
I have tried the following with the create table combined and I have also use two separate lines and neither has succeeded in creating the images table. The Inventory table is created but not the second table.

put getDatabaseID() into tDatabaseID
put "CREATE TABLE Inventory(Description char(360), DatePurchased Date(10) , PurchasePrice Integer(10),Id INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TABLE Images(Id INTEGER PRIMARY KEY AUTOINCREMENT, Image BLOB , InventoryId INTEGER FOREIGN KEY REFERENCES INVENTORY(Id))" into tSQL
revExecuteSQL tDatabaseID, tSQL


Thanks Stacy

[Dixie]

Mmm... I create each table one at a time, for example...

Code: Select all

    /* if the database does not exist then create it and build the tables */
      put revOpenDatabase("sqlite", "ptlsdb.sqlite", "binary") into connID
      
      put "CREATE TABLE events(id integer primary key autoincrement not null,icu_id integer, nrd_original integer," & \
            " nrd_adjusted integer, actual integer,type text, duration integer, result text,icu_level integer)" into tSQL
      revExecuteSQL connID, tSQL
      put "CREATE TABLE icus(id integer primary key autoincrement not null,source_id integer, question text, clue text," & \
            " answer text,tags text, level integer, nrd_original integer, nrd_adjusted integer,archived text, deferred text)" into tSQL
      revExecuteSQL connID, tSQL

[Klaus]

Yep, looks like we cannot put 2 "create table" commands in 1 sql query in Livecode/SQLite!?
Could be worse!

[SChamblee]

Klaus

I had tested separate sql statement and didn't have any luck, so I will have to take another look at my sql statements.

I give your code a test too, maybe I can see what I did wrong.

Thanks for the help

Stacy

[SChamblee]

The issue that causes the table not to be created is the Foreign Key Reference statement. https://www.sqlite.org/foreignkeys.html

I assume this is not supported in LC.

[MaxV]

SQlite doesn't support foreign keys (they are evil), however you can activate them each time you start a connection:

PRAGMA foreign_keys; test if foreign keys are active: 0 = no, 1=yes

PRAGMA foreign_keys = ON; activate foreign keys

Note:

If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys at all.