CODEWITHSUNDEEP
javaandroidsqlite
SilverWolfe
SilverWolfe

Reputation: 85

sqlite table creation

Im unsure of my problem. I am getting no such table when queriyng the second table.. these are both within the onCreate method

db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_CBID
                   + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME
                   + " TEXT NOT NULL, " + KEY_RACE + " TEXT NOT NULL,"
                   + KEY_CLASS + " TEXT NOT NULL," + KEY_DEITY
                   + " TEXT NOT NULL," + KEY_GENDER + " TEXT NOT NULL,"
                   + KEY_HEIGHT + " TEXT NOT NULL," + KEY_WEIGHT
                   + " TEXT NOT NULL);");

        db.execSQL("CREATE TABLE " + DATABASE_TABLE2 + " (" + KEY_CSID
                   + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_SKILL
                   + " TEXT NOT NULL, " + KEY_CBID + " INTEGER PRIMARY KEY FOREIGN KEY);"
                   );

Edit: showing cvs

String skill = "blah test";
    Cursor c = ourDatabase.rawQuery("SELECT " + KEY_CBID + " FROM " + DATABASE_TABLE + " order by " + KEY_CBID + " DESC limit 1", null);
    if (c != null)
    {
        c.moveToFirst();
        cbid = c.getInt(0);
    }

    ContentValues cv = new ContentValues();
    cv.put(KEY_SKILL, skill);
    cv.put(KEY_CBID, cbid);
    return ourDatabase.insert(DATABASE_TABLE2, null, cv);

My select statements is:

Cursor c = ourDatabase.rawQuery("SELECT " + KEY_SKILL + ", " + KEY_NAME + ", " + KEY_CBID + " FROM " + DATABASE_TABLE + ", " + DATABASE_TABLE2 + " WHERE " + DATABASE_TABLE +"."+KEY_CBID+" = " +DATABASE_TABLE2+"."+KEY_CBID+" && " +DATABASE_TABLE+"."+KEY_NAME+" = '"+item+"'", null);

Upvotes: 1

Views: 267

Answers (2)

png
png

Reputation: 4408

  1. check you log cat to see if there is an error while creating the second table. If there is an error from sqllite, it will be logged in logcat

  2. When a new table is added to DB, make sure DB is upgraded IF from your code if you are calling db.open , if the database is already created it will be open. So in this case the code to create new table will not be hit. So you have to make sure that you upgrade the database where you can delete all your existing tables and create again

    @Override public void onCreate(SQLiteDatabase db) {

        createAllTables(db);
    Log.v(DBTAG, "DATABASE CREATED");
    // Toast.makeText(mCtx, "created", Toast.LENGTH_SHORT).show();
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(DBTAG, "Upgrading database from version " + oldVersion
            + " to " + newVersion + ", which will destroy all old data");
    dropExistingTables(db);
    onCreate(db);
}

private void dropExistingTables(SQLiteDatabase db) {

    for (int i = 0; i < Utils.getDropTableQueries().size(); i++) {
        db.execSQL(Utils.getDropTableQueries().get(i));
    }
}

Thus to conclude make sure you are creating the table, and that no error while creating.

Upvotes: 0

Sam
Sam

Reputation: 86948

I don't believe that your second table is being created, here is how to declare a table with a foreign key:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

I took these from SQLite.org.

Also foreign keys are disabled by default in SQLite, for Android use:

db.execSQL("PRAGMA FOREIGN_KEYS=ON;");

before inserting data, if you haven't already;

Upvotes: 2

Related Questions