syntax error in creat table in sqlite database

Question

I somehow cannot create a table. I've checked my syntax and searched other people having the same error but I can't find what the error is.I use same syntax for creation all table but just stuff table has problem.Even I change database version to 2 and several times uninstall my app.I put a part of code here.

public class Stuff {
//*****DataBase query*****/
public static final String TABLE_NAME = "stuff";
public static final String CLM_ID = "id";
public static final String CLM_NAME = "name";
public static final String CLM_PRICE = "price";
public static final String CLM_DESCRIPTION = "description";
public static final String CLM_BRAND = "brand";
public static final String CLM_MODEL = "model";
public static final String CLM_COUNT = "count";
public static final String CLM_COLOR = "color";
public static final String CLM_CATGID = "catgid";

public static final String SQL_DROP_TABLE = String.format("DROP TABLE %s", TABLE_NAME);
public static final String SQL_CREATE_TABLE = String.format("CREATE TABLE IF NOT EXISTS %s (" +
        "%s INTEGER PRIMARY KEY," +
        "%s TEXT," +
        "%s REAL," +
        "%s TEXT NOT NULL," +
        "%s TEXT NOT NULL," +
        "%s TEXT," +
        "%s INTEGER," +
        "%s TEXT NOT NULL," +
        "FOREIGN KEY (%s) REFERENCES(%s)" +
        ");", TABLE_NAME, CLM_ID, CLM_NAME,CLM_PRICE,CLM_DESCRIPTION,CLM_BRAND,CLM_MODEL,CLM_COUNT,CLM_COLOR,CLM_CATGID,Category.TABLE_NAME);
}
public class DBHelper extends SQLiteOpenHelper {
  private static DBHelper helper;
private static SQLiteDatabase database;

public static final String DATA_BASE_NAME = "dbDigikala";
public static final int DATA_BASE_VERSION = 1;

public static SQLiteDatabase getDatabase(Context context) {
    return getInstance(context).getWritableDatabase();
}

public static DBHelper getInstance(Context context) {
    if (helper == null) {
        helper = new DBHelper(context, DATA_BASE_NAME, null, DATA_BASE_VERSION);
    }
    return helper;
}

public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(Category.SQL_CREATE_TABLE);
    db.execSQL(Stuff.SQL_CREATE_TABLE);
    db.execSQL(Rate.SQL_CREATE_TABLE);
    db.execSQL(Favorit.SQL_CREATE_TABLE);
    db.execSQL(Image.SQL_CREATE_TABLE);
    db.execSQL(Video.SQL_CREATE_TABLE);
    db.execSQL(Address.SQL_CREATE_TABLE);
    db.execSQL(User.SQL_CREATE_TABLE);
    db.execSQL(Comment.SQL_CREATE_TABLE);
    db.execSQL(Factor.SQL_CREATE_TABLE);
    db.execSQL(SoldStuff.SQL_CREATE_TABLE);
    db.execSQL(Slake.SQL_CREATE_TABLE);
    db.execSQL(SlakeStuff.SQL_CREATE_TABLE);
    db.execSQL(SlakeUser.SQL_CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)    {
    db.execSQL(Category.SQL_DROP_TABLE);
    db.execSQL(Stuff.SQL_DROP_TABLE);
    db.execSQL(Rate.SQL_DROP_TABLE);
    db.execSQL(Favorit.SQL_DROP_TABLE);
    db.execSQL(Image.SQL_DROP_TABLE);
    db.execSQL(Video.SQL_DROP_TABLE);
    db.execSQL(Address.SQL_DROP_TABLE);
    db.execSQL(User.SQL_DROP_TABLE);
    db.execSQL(Comment.SQL_DROP_TABLE);
    db.execSQL(Factor.SQL_DROP_TABLE);
    db.execSQL(SoldStuff.SQL_DROP_TABLE);
    db.execSQL(Slake.SQL_DROP_TABLE);
    db.execSQL(SlakeStuff.SQL_DROP_TABLE);
    db.execSQL(SlakeUser.SQL_DROP_TABLE);
    onCreate(db);
  }
}

public class Config extends Application {
public static Context context;
public static Activity currentActivity;
public static final long SPLASH_DELAY = 2000;
public static SQLiteDatabase database;
@Override
public void onCreate() {
    super.onCreate();
    context = getApplicationContext();
    database = DBHelper.getDatabase(getApplicationContext());
}
}

Could this be the problem? It looks like the foreign key hasn't been defined? [You have to define your column first and then set foreign key on it.](https://stackoverflow.com/questions/5289861/sqlite-android-foreign-key-syntax) — Elletlar, Jul 25 '18 at 13:37
Please don't post screenshots of code, XML, or logcat output. Please post all text as text. — Mike M., Jul 25 '18 at 23:07

Viraj Patel · Accepted Answer · 2018-07-25T14:11:58.107

1

Problem is where you are setting ForeignKey "FOREIGN KEY (%s) REFERENCES(%s)".
Update your code as follow:

public static final String SQL_CREATE_TABLE = String.format("CREATE TABLE IF NOT EXISTS %s (" +
        "%s INTEGER PRIMARY KEY," +
        "%s TEXT," +
        "%s REAL," +
        "%s TEXT NOT NULL," +
        "%s TEXT NOT NULL," +
        "%s TEXT," +
        "%s INTEGER," +
        "%s TEXT NOT NULL," +
        "FOREIGN KEY (%s) REFERENCES %s(%s)" +
        ");", TABLE_NAME, CLM_ID, CLM_NAME,CLM_PRICE,CLM_DESCRIPTION,CLM_BRAND,CLM_MODEL,CLM_COUNT,CLM_COLOR,CLM_CATGID,Category.TABLE_NAME,Category.CLM_ID);

You are passing only table name and setting it at a wrong place.

"FOREIGN KEY (%s) REFERENCES %s(%s)"

Here first %s after REFERENCES is of Table Name and second (%s) is of Column id which you have in Category Table.

edited Jul 25 '18 at 14:11

answered Jul 25 '18 at 13:51

Viraj Patel

2,113
16
23

I did this, but my app give me same error. I think the problem comes from somewhere else. for example `dBHelper` class. – maryam Jul 25 '18 at 14:04
What is the id (primary key) in your Category table? You have to pass that column name at last instead of `Category.CLM_ID`. And also try to clear data and install the latest build once. – Viraj Patel Jul 25 '18 at 14:07
The primary key of Category table is That's what you wrote (`Category.CLM_ID`). I also clear data . – maryam Jul 25 '18 at 14:10
Try this `"FOREIGN KEY (%s) REFERENCES %s(%s)"` remove space after %s – Viraj Patel Jul 25 '18 at 14:12
Happy to help you!! – Viraj Patel Jul 25 '18 at 17:35

score 1 · Answer 2 · answered Jul 25 '18 at 14:04

The clause REFERENCES must use the syntax: REFERENCES table_name(column_id):

public static final String SQL_CREATE_TABLE = String.format("CREATE TABLE IF NOT EXISTS %s (" +
        "%s INTEGER PRIMARY KEY," +
        "%s TEXT," +
        "%s REAL," +
        "%s TEXT NOT NULL," +
        "%s TEXT NOT NULL," +
        "%s TEXT," +
        "%s INTEGER," +
        "%s TEXT NOT NULL," +
        "FOREIGN KEY (%s) REFERENCES %s(%s)" +
        ");", TABLE_NAME, CLM_ID, CLM_NAME,CLM_PRICE,CLM_DESCRIPTION,CLM_BRAND,CLM_MODEL,CLM_COUNT,CLM_COLOR,CLM_CATGID,Category.TABLE_NAME,Category.CLM_ID);

syntax error in creat table in sqlite database

2 Answers2