samedi 27 décembre 2014

why sqlite insertion detects conflict when the relation is one to many?

I want to add some rows in comment table to the same id in phone table.


however it seems my query allows only one row per phone_id.


how can I fix this?


That's strange as I didn't declare a primary key or unique key in the comment table


So the relation phone-comment should be one to many.



public class CommentDal extends SQLiteOpenHelper {

// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = Constants.DB_NAME;

private static final String COMMENTS_TABLE = "COMMENTS_TABLE";

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

@Override
public void onCreate(SQLiteDatabase db) {
// SQL statement to create book table

String CREATE_COMMENTS_TABLE =
"CREATE TABLE " + COMMENTS_TABLE +
" ( "+ KEY_ID+" INTEGER, "
+ KEY_COMMENT_TEXT+" TEXT, "
+ "FOREIGN KEY("+KEY_ID+") REFERENCES "+PhoneDal.BLOCKED_PHONES_TABLE+"("+KEY_ID+"))";

db.execSQL(CREATE_COMMENTS_TABLE);

}


private static final String KEY_ID = "id";
private static final String KEY_COMMENT_TEXT = "KEY_COMMENT_TEXT";

public void addItem(long phoneID, String comment) {
Log.d(Constants.LOGGER_TAG, "add saved-offer");
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();

// 2. create ContentValues to add key "column"/value
ContentValues values = new ContentValues();
values.put(KEY_ID, phoneID);
values.put(KEY_COMMENT_TEXT, comment);

// 3. insert
long newRowId = db.insertWithOnConflict(COMMENTS_TABLE, null, values,
SQLiteDatabase.CONFLICT_REPLACE);
// 4. close
db.close();

Aucun commentaire:

Enregistrer un commentaire