title: Android SQLite Database Foreign Key Does Not Work
body: <p>I'm working with the Android SQLite Database and I have two Tables in it.</p>
  
  <p>Table <code>CHECKPOINTS</code> defines a foreign key to an attribute in Table <code>TOUR</code>.</p>
  
  <p>After storing data to the database the table <code>Tour</code> is displayed correctly. But, the column with the foreign key <code>TOUR_ID</code> in table <code>CHECKPOINTS</code> remains blank. I don't know why.</p>
  
  <p>The syntax should be ok and I also set the <code>PRAGMA foreign_keys = ON;</code>.
  After experimenting a while I defined a <code>Trigger</code> in the <code>onCreate()</code> function of my <code>DatabaseHelper</code>. </p>
  
  <p>But, now I get an Error:</p>
  
  <p><code>android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0</code>.</p>
  
  <p>DatabaseHelper: </p>
  
  <pre><code>private static final String TABLE_CREATE_TOUR = "create table TOUR("
          + " ID integer primary key autoincrement, "
          + " NAME varchar(20)) ";
  
  
  private static final String TABLE_CREATE_CHECKPOINT = "create table CHECKPOINT("
          + " ID integer primary key autoincrement, "
          + " TOUR_ID integer not null, "
          + " FOREIGN KEY (TOUR_ID) REFERENCES TOUR(ID)) ";
  
  
  public void onCreate(SQLiteDatabase database) {
      database.execSQL(TABLE_CREATE_TOUR);
      database.execSQL(TABLE_CREATE_CHECKPOINT);
  
      database.execSQL("CREATE TRIGGER fk_checkpoint_tourid BEFORE INSERT ON CHECKPOINT "
                      + " FOR EACH ROW BEGIN "
                      + " SELECT CASE WHEN ((SELECT ID FROM TOUR WHERE ID = new.TOUR_ID ) IS NULL) "
                      + " THEN RAISE (ABORT, 'Foreign Key Violation') END; "
                      + " END;");
  
  }
  </code></pre>
  
  <p>DataSource:</p>
  
  <pre><code>public void open() throws SQLException {
      database = dbhelper.getWritableDatabase();
  
      // Enable foreign key constraints
      if (!database.isReadOnly()) {
           database.execSQL("PRAGMA foreign_keys = ON;");
      } 
  }
  </code></pre>
  
  <p>Does anyone have an idea, on how to get this thing going ? </p>
  
  <p>EDIT:</p>
  
  <p>Insert in Table <code>TOUR</code>:</p>
  
  <pre><code>    public long InsertTour(String name){
      ContentValues values = new ContentValues();
      values.put("NAME", name);
  
      return database.insert("TOUR", null, values);       
  }
  </code></pre>
  
  <p>The Table <code>CHECKPOINT</code> contains more data as described above. Here's the insert:</p>
  
  <pre><code>public Entry createEntry(String PARAM1, String PARAM2, long PARAM3, String PARAM4) {
      ContentValues values = new ContentValues();
      values.put("COLUMN1", PARAM1);
      values.put("COLUMN2", PARAM2);
      values.put("COLUMN3", PARAM3);
      values.put("COLUMN4", PARAM4);
  
      long insertId = database.insert("CHECKPOINT", null, values);
  
      Cursor cursor = database.query("CHECKPOINT", allColumns, "ID = " + insertId, null, null, null, null);
      cursor.moveToFirst();
  
      return cursorToEntry(cursor);
  }
  </code></pre>