How to do multiple column unique-constraint in ormlite ( SQLite )

Question

I'm using ormlite for Android and I'm trying to get a multiple column unique-constraint. As of now i'm only able to get a unique constraint on indiviudal columns like this:

CREATE TABLE `store_group_item` (`store_group_id` INTEGER NOT NULL UNIQUE ,
    `store_item_id` INTEGER NOT NULL UNIQUE ,
    `_id` INTEGER PRIMARY KEY AUTOINCREMENT );

and what I want is

CREATE TABLE `store_group_item` (`store_group_id` INTEGER NOT NULL ,
    `store_item_id` INTEGER NOT NULL ,
    `_id` INTEGER PRIMARY KEY AUTOINCREMENT,
    UNIQUE( `store_group_id`, `store_item_id` );

In my model I've been using the following annotations for the unique columns:

@DatabaseField( unique = true )

Is there a way to get this to work?

score 14 · Accepted Answer · answered Aug 19 '11 at 22:55

14

How about using

@DatabaseField (uniqueCombo = true) 
String myField;

annotation instead - is it a matter of the uniqueIndexName being faster when accessing items in the table?

answered Aug 19 '11 at 22:55

Ready4Android

2,022
2
25
29

This is the right answer if you have version >= 4.20 which was added 4/30/2011. http://ormlite.com/changelog.txt. Good find! – Pzanno Aug 22 '11 at 21:03
Oh thanks :) but like I said - maybe Gray could clarify if there is a performance catch to this. – Ready4Android Aug 25 '11 at 23:10

Gray · Answer 2 · 2011-09-26T15:25:40.713

Edit:

As @Ready4Android pointed out, we've since added in version 4.20 support for uniqueCombo annotation field. Here are the docs:

http://ormlite.com/docs/unique-combo

There should be no performance differences between using this mechanism versus the uniqueIndexName mentioned below.

Yes. You can't do this with the unique=true tag but you can with a unique index.

@DatabaseField(uniqueIndexName = "unique_store_group_and_item_ids")
int store_group_id;
@DatabaseField(uniqueIndexName = "unique_store_group_and_item_ids")
int store_item_id;

This will create an index to accomplish the unique-ness but I suspect that the unique=true has a hidden index anyway. See the docs:

http://ormlite.com/docs/unique-index

I will look into allowing multiple unique fields. May not be supported by all database types.

are these annotations possible with foreign Objects? – B. TIger Jun 25 '13 at 22:25 — B. TIger, Jun 25 '13 at 22:25

How to do multiple column unique-constraint in ormlite ( SQLite )

2 Answers2

Linked