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?
To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
SQL UNIQUE constraint for 2 columns example Notice that we named the UNIQUE constraints using CONSTRAINT keyword. We can use this name to remove the UNIQUE constraint later if we want. To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.
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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With