android - 如何避免 Sqlite 数据库中的冗余？

Question

我为书签/收藏夹(按钮)创建了一个数据库(sqlite)。该表只有 2 行(id、书签)。每次我单击书签按钮时，它都会在书签列中添加相同的书签(字符串)。

这是我的数据库代码(DatabaseHelper.java):

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "Bookmark.db";
public static final String TABLE_NAME = "bookmark_table";
public static final String COL_1 = "ID";
public static final String COL_2 = "BOOKMARK";


public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_NAME + " (ID INTEGER PRIMARY KEY   AUTOINCREMENT, BOOKMARK TEXT)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i2) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

public boolean insertData(String bookmark){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_2, bookmark);
    long result = db.insert(TABLE_NAME, null, contentValues);
    if(result == -1)
        return false;
    else
        return true;
}

public Cursor getAllData(){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from " + TABLE_NAME, null);
    return res;
}
}

如何使“书签”列唯一？

Answer 1

只需在列描述后添加UNIQUE，如下所示:

db.execSQL("create table " + TABLE_NAME + " (ID INTEGER PRIMARY KEY  AUTOINCREMENT, BOOKMARK TEXT UNIQUE)");