5 Answers

• Accepted Answer

  Tjeu

  You are inserting a null value into a column that has a NOT NULL constraint:

  INSERT INTO Level1 VALUES (null, "Test1", "Test1", 1);
  INSERT INTO Level1 VALUES (null, "Test2", "Test2", 2);
  

  Either you need to create a GUID, or use an autoincrement value, using an approach demonstrated below, which will run if you paste it into a blank app.js:

  var db = Ti.Database.open('autoincrement_test');
  db.execute('CREATE TABLE IF NOT EXISTS table1 (fieldA INT, fieldB TEXT)');
  db.execute('DELETE FROM table1');
  db.execute('INSERT INTO table1 (fieldA, fieldB) VALUES (?,?)', 123,'this is row 1');
  db.execute('INSERT INTO table1 (fieldA, fieldB) VALUES (?,?)', 234,'this is row 2');
  db.execute('INSERT INTO table1 (fieldA, fieldB) VALUES (?,?)', 345,'this is row 3');
  db.execute('INSERT INTO table1 (fieldA, fieldB) VALUES (?,?)', 456,'this is row 4');
  
  var testRS = db.execute('SELECT ROWID,fieldA,fieldB FROM table1');
  while (testRS.isValidRow())
  {
    var myKey = testRS.fieldByName('ROWID');
    var fieldA = testRS.fieldByName('fieldA');
    var fieldB = testRS.fieldByName('fieldB');
    Ti.API.info('row data = ' + myKey + ' / ' + fieldA + ' / ' + fieldB);
    testRS.next();
  }
  testRS.close();
  db.close();
  

  Note that FK support is only available in Android 2.2+, as it includes SQLite 3.6.19 which introduces it. It's off by default, and you must enable it at runtime (every time you access the DB). See Enabling Foreign Key Support in the official docs for more info.

  So, when you create a connection to the db, you must run:

  db.execute('PRAGMA foreign_keys = ON');
  

  Adding to the above code, you may be able to add the FK constraint to a child table, although admittedly I've not tried this:

  db.execute('CREATE TABLE IF NOT EXISTS table2 (fieldA INT, fieldB TEXT, fieldC INTEGER REFERENCES table1 (ROWID))');
  

  It would be useful if you would report back about whether it was successful.

  Cheers

• No, I don't have this problem. Please post some code, and also state the versions of the software you are using.

• I am using the 0.6.8 version of the Firefox SQLite manager.

  Here's some code:

  I create my first table:

  CREATE TABLE Level1(
    Level1ID            INTEGER PRIMARY KEY NOT NULL, 
    Code                              TEXT NOT NULL,
    Description                     TEXT NOT NULL,
    SortOrder                        INTEGER NOT NULL
  );
  

  Now I create a second table, with references to Level1ID

  CREATE TABLE Level2(
    Level2ID            INTEGER PRIMARY KEY NOT NULL, 
    Level1Id            INTEGER NOT NULL,
    Code                              TEXT NOT NULL,
    Description                     TEXT NOT NULL,
    SortOrder                        INTEGER NOT NULL,
    FOREIGN KEY(Level1Id) REFERENCES Level1(ID)
  );
  

  So now i put some data into my Level1, after that I try to insert data into level 2, but then I get the error 'Mismatch'.

  Here's my insert statements:

  Level1:

  INSERT INTO Level1 VALUES (null, "Test1", "Test1", 1);
  INSERT INTO Level1 VALUES (null, "Test2", "Test2", 2);
  

  Level2:

  INSERT INTO KeywordLevel2 VALUES (null,  1, "Level2Test1", "Level2Test1", 1);
  INSERT INTO KeywordLevel2 VALUES (null,  2, "Level2Test2", "Level2Test2", 2);
  

  Thanks!

• I see the problem on the foreign key. I referenced to ID instead of Level1ID.

  But I still can't find out how to default set the value to Foreign Keys ON.
  It always switches back to off.

  Do you happen to know how I can do that?

  Thanks!

• Sorry, Refreshed the page and it re-posted my previous answer.