why i fail to create this table on android SQLite?

Question

I created 4 table on my app. Below is the creation of the last table -

this is the query that i get after the parse of the string

CREATE TABLE IF NOT EXISTS TABLE_1 (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person ) REFERENCES TABLE_2 , COLUMN_INDEX_AS_TEXT TEXT NOT NULL, FOREIGN KEY( COLUMN_INDEX_AS_TEXT ) REFERENCES TABLE_3 );

When i run this method of creation - i get an exception that the value near the COLUMN_INDEX_AS_TEXT is

Error Code : 1 (SQLITE_ERROR) Caused By : SQL(query) error or missing database. (near "COLUMN_INDEX_AS_TEXT ": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS ..... ( the query )

I check and all the table that i create before this table are exist.

  try
    {
        SQLiteDatabase db = getWritableDatabase();



String query = "CREATE TABLE IF NOT EXISTS " + TABLE_1 + "("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person )" + " REFERENCES " + TABLE_2 + ", "
                + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL, FOREIGN KEY( " +  COLUMN_INDEX_AS_TEXT + " ) REFERENCES " + TABLE_3 + " );";




db.execSQL(query);
    }
    catch (Exception e)
    {
        Log.e(TAG, e.getMessage());
    }

Answer 1

In SQL as understood by sqlite, table constrainsts such as FOREIGN KEY follow column specifiations. You cannot intermix them. Move the FOREIGN KEY specs at the end of your CREATE TABLE so that the COLUMN_INDEX_AS_TEXT column spec is before them.

Answer 2

You should create or call create on the referenced tables first before creating this table with multiple foreign keys.

I tested the query by using SQLiteOpenHelper class see below:

    public class DbHelper extends SQLiteOpenHelper {

    private static final String DATABASENAME = "eeee.db";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_1 = "tb1";
    private static final String TABLE_2 = "tb2";
    private static final String TABLE_3 = "tb3";
    private static final String COLUMN_INDEX_AS_TEXT = "indexxx";

    public DbHelper(Context context) {
        super(context, DATABASENAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        String mainquery = "CREATE TABLE IF NOT EXISTS " + TABLE_1 + "("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person )" + " REFERENCES " + TABLE_2 + ", "
                + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL, FOREIGN KEY( " +  COLUMN_INDEX_AS_TEXT + " ) REFERENCES " + TABLE_3 + " );";

        String query2 = "CREATE TABLE IF NOT EXISTS " + TABLE_2 + "("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "PK_Person TEXT NOT NULL );";

        String query = "CREATE TABLE IF NOT EXISTS " + TABLE_3 + "("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL);";

        sqLiteDatabase.execSQL(query);
        sqLiteDatabase.execSQL(query2);
        sqLiteDatabase.execSQL(mainquery);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
// you can specify your own implementation here if db version incremented
    }
}

Answer 3

this is your code:

String mainquery = "CREATE TABLE IF NOT EXISTS " + TABLE_1 + "("
            + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
            + "PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person )" + " REFERENCES " + TABLE_2 + ", "
            + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL, FOREIGN KEY( " +  COLUMN_INDEX_AS_TEXT + " ) REFERENCES " + TABLE_3 + " );";

add a references to table,but not found column name. you can try:

 String mainquery = "CREATE TABLE IF NOT EXISTS " + TABLE_1 + "("
            + "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
            + "PK_Person TEXT NOT NULL, FOREIGN KEY ( PK_Person )" + " REFERENCES " + TABLE_2 + ", "
            + COLUMN_INDEX_AS_TEXT  + " TEXT NOT NULL, FOREIGN KEY( " +  COLUMN_INDEX_AS_TEXT + " ) REFERENCES " + TABLE_3 + "("+COLUMN_NAME+")" );";