Android SQLite ForeignKey Column not found in Database

Question

After the program runs Successfully and i extract .db file from Android Device monitor,I can find all my Tables and columns for each tables except the Column for Foreign key.Can anyone why this is happening? Is there something wrong with my Query? Here is my Code:

 private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "datacollector.db";

private static final String Table_Survey = "SURVEY";
private static final String Survey_KEY_ID = "id";
private static final String Survey_KEY_NAME = "Survey_Name";
private static final String KEY_Table_Name = "Table_Name";


private static final String Table_formDetails = "FormDetails";
private static final String formDetails_KEY_ID = "id";
private static final String formDetails_field_Type="Field_Type";
private static final String formDetails_label = "Label";
private static final String formDetails_Options = "Options";

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

@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_SURVEY_TABLE = "CREATE TABLE " + Table_Survey + "("
            + Survey_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + Survey_KEY_NAME + " VARCHAR(30),"
            + KEY_Table_Name + " VARCHAR(50)" + ")";
    db.execSQL(CREATE_SURVEY_TABLE);


    String CREATE_FORM_DETAILS_TABLE = "CREATE TABLE " + Table_formDetails + "("
            + formDetails_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + formDetails_field_Type + " VARCHAR(30),"
            + formDetails_Options + " VARCHAR(30),"
            + formDetails_label + " VARCHAR(30),"
            + " FOREIGN KEY ("+Survey_KEY_ID+") REFERENCES "+Table_Survey+"("+Survey_KEY_ID+"));";
    db.execSQL(CREATE_FORM_DETAILS_TABLE);



}

score 2 · Accepted Answer · answered Sep 01 '16 at 05:21

2

its because you have not added survey_key_id in CREATE_FORM_DETAILS_TABLE

String CREATE_FORM_DETAILS_TABLE = "CREATE TABLE " + Table_formDetails + "("
            + formDetails_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + formDetails_field_Type + " VARCHAR(30),"
            + formDetails_Options + " VARCHAR(30),"
            + formDetails_label + " VARCHAR(30),"
            + Survey_KEY_ID + " INTEGER,"
            + " FOREIGN KEY ("+Survey_KEY_ID+") REFERENCES "+Table_Survey+"("+Survey_KEY_ID+"));";
    db.execSQL(CREATE_FORM_DETAILS_TABLE);

answered Sep 01 '16 at 05:21

Pavya

6,015
4
29
42

There is error "duplicate column name: id (code 1): , while compiling: CREATE TABLE FormDetails(id INTEGER PRIMARY KEY AUTOINCREMENT,Field_Type VARCHAR(30),Options VARCHAR(30),Label VARCHAR(30),id INTEGER, FOREIGN KEY (id) REFERENCES SURVEY(id));'' – Niroj Sep 01 '16 at 05:35
+ Survey_KEY_ID + " INTEGER," change name here and in FOREIGN KEY ("+Survey_KEY_ID+") – Pavya Sep 01 '16 at 05:36
just change in declaration private static final String formDetails_KEY_ID = "id"; to private static final String formDetails_KEY_ID = "detail_id"; – Pavya Sep 01 '16 at 05:41

brahmy adigopula · Answer 2 · 2016-09-01T05:55:09.233

you are mapping same table column to foreign key.try this now.

 private static final int DATABASE_VERSION = 1;
 private static final String DATABASE_NAME = "datacollector.db";
private static final String Table_Survey = "SURVEY";
private static final String Survey_KEY_ID = "id";
private static final String Survey_KEY_NAME = "Survey_Name";
private static final String KEY_Table_Name = "Table_Name";
private static final String Table_formDetails = "FormDetails";
private static final String formDetails_KEY_ID = "id";
private static final String formDetails_field_Type="Field_Type";
private static final String formDetails_label = "Label";
private static final String formDetails_Options = "Options";

public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_SURVEY_TABLE = "CREATE TABLE " + Table_Survey + "("
            + Survey_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + Survey_KEY_NAME + " VARCHAR(30),"
            + KEY_Table_Name + " VARCHAR(50)" + ")";
    db.execSQL(CREATE_SURVEY_TABLE);
    String CREATE_FORM_DETAILS_TABLE = "CREATE TABLE " + Table_formDetails + "("
            + formDetails_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + formDetails_field_Type + " VARCHAR(30),"
            + formDetails_Options + " VARCHAR(30),"
            + formDetails_label + " VARCHAR(30),"
             + forign_Survey_KEY_ID + "INTEGER DEFAULT 0,"
            + " FOREIGN KEY ("+forign_Survey_KEY_ID+") REFERENCES "+Table_Survey+"("+Survey_KEY_ID+"));";
    db.execSQL(CREATE_FORM_DETAILS_TABLE);
}

forign_Survey_KEY_ID, can you explain what is this? i got error here or is this not initialized? — Niroj, Sep 01 '16 at 05:50
you need to have a column which to store the id's of super table so you have to create a column to store them.i edited the code for your error,**not initialized**.check out now — brahmy adigopula, Sep 01 '16 at 05:57

Android SQLite ForeignKey Column not found in Database

2 Answers2