sqliteOpenHelper...Unique combination of columns?

[Nightpoison]

Hello.

I'm looking for some direction on creating a table that has uses UNIQUE constraints. When adding new rows to my database, I want to make sure I don't add a duplicate value. However, I need to check that the combination of two columns is unique.

for example the column names are as follows.

KEY_RECORD     KEY_FILE     KEY_TIME     KEY_TEMP     KEY_CONC

KEY_RECORD will repeat over 7 rows, with a the KEY_FILE iterating 1-7 such as

KEY_RECORD     KEY_FILE     KEY_TIME     KEY_TEMP     KEY_CONC
0001              1
0001              2
...
0001              7
0002              1
0002              2
....

currently I'm creating my DB using sqliteOpenHelper. I do the following.

private static final String TABLE_CREATE = "CREATE TABLE " + TABLE_RECORDS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_RECORD + " TEXT," + KEY_FILE + " TEXT," + KEY_TIME + " TEXT," + KEY_TEMP + " TEXT," + KEY_CONC + " TEXT" + " )";

...

@Override
public void onCreate (SQLiteDatabase db)
{

    db.execSQL(TABLE_CREATE);
    db.close();
}

I know if I add the keyword UNIQUE I can make any of the columns unique.

private static final String TABLE_CREATE = "CREATE TABLE " + TABLE_RECORDS + "(... + " TEXT UNIQUE," + KEY_FILE + ..." + " )"

But how do I do it so that a combination of the two columns must be unique in order to add.

 

[23tony]

Does this help?
https://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns

Looks like you just need to put two values in your PRIMARY KEY definition. So drop this:

"(" + KEY_ID + " INTEGER PRIMARY KEY," ...

for

"(" + KEY_ID + " INTEGER," ...

and define the primary key at the end:

 ... "KEY_CONC + " TEXT" + " PRIMARY KEY(" + KEY_ID + ", " + KEY_RECORD + ") )"

 

[23tony]

Of course, that only helps if you need the unique constraint to be the primary key, but your question reads like that's what you want.

 

[Nightpoison]

I had no idea that you could use more than one column as a primary key.

placing

PRIMARY KEY(" + KEY_ID + ", " + KEY_RECORD + ")

at the end of looks to be very similiar to what I found on using UNIQUE for mutliple columns. It looks like my issue with using UNIQUE had more to do with formatting then using it wrong.

Thanks that helps. I'll make the changes and see where it takes me. Thank you!!!

Does this help?
https://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns

Looks like you just need to put two values in your PRIMARY KEY definition. So drop this:

"(" + KEY_ID + " INTEGER PRIMARY KEY," ...

for

"(" + KEY_ID + " INTEGER," ...

and define the primary key at the end:

 ... "KEY_CONC + " TEXT" + " PRIMARY KEY(" + KEY_ID + ", " + KEY_RECORD + ") )"

 

[23tony]

Fill us in if it worked!

 

[Nightpoison]

Sorry about that. Ultimately it was a string formatting issue that was causing the original error. I did implement the database with a two column primary key, which I thought was more appropriate. It works well and I'm not seeing any of the duplicates.

with that being said, I didn't need to go that route. Using UNIQUE would have worked as well. but your suggestion I liked much better.

I've been tripped up several times in regards to string formatting when it comes to writing the SQL commands.