CODEWITHSUNDEEP
javasqlsqliteandroid-sqliteunique-constraint
RuntimeException
RuntimeException

Reputation: 1241

make two columns as unique in sqlite

I have one table with 3 columns. columns are 

BaseColumns._ID | NATIVE_CONTACT_ID | HIDDEN_TYPE

in these, BaseColumns._ID is a primary key. What I want is, I don't want to allow developer to insert a duplicate value into NATIVE_CONTACT_ID, if he tries to insert, then old value of HIDDEN_TYPE column value corresponding to NATIVE_CONTACT_ID should be overriden with new value.

Let me clarify you more. for example, my table looks like this after few insertions.

_ID  | NATIVE_CONTACT_ID | HIDDEN_TYPE
---------------------------------------
  1        23                 1
  2        24                 0
  3        25                 1
  4        26                 0

Now when developer wants to insert a new row with NATIVE_CONTACT_ID having value 25 and having HIDDEN_TYPE value 0, then old value of HIDDEN_TYPE should change to this new value. here it should change to 0 from 1, I don't want to allow duplicate insertions of NATIVE_CONTACT_ID.

I hope I explained my problem clearly.

Now my question is, What are the keywords and SQL statement I need to use to create a such table?

This is what I have tried...

    public static final String TABLE_NAME = "hidden_info";
    public static final String HIDDEN_TYPE = "hidden_type";
    public static final String NATIVE_CONTACT_ID = "native_contact_id";
    public static final String _ID = BaseColumns._ID;

    public static void onCreate(SQLiteDatabase database) {
        String query = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                + NATIVE_CONTACT_ID + " INTEGER, " + HIDDEN_TYPE
                + " INTEGER)";
        database.execSQL(query);
    }

Upvotes: 1

Views: 313

Answers (1)

Mohsen Kamrani
Mohsen Kamrani

Reputation: 7457

CREATE TABLE name (column defs, UNIQUE (NATIVE_CONTACT_ID) ON CONFLICT REPLACE);

For more information please see here.

Upvotes: 5

Related Questions