Cannot set Nullable UNIQUE constraint on a column (SQLite on Android)

[curioustechizen]

I'm trying to create a column which has the following constraints:

1. The column contains UNIQUE values
2. The column can have NULL values.
3. This is not the PRIMARY KEY column.

Note that SQLite allows NULL values in columns designated as UNIQUE - Reference

Since Orman uses the @Index annotation do designate UNIQUE-ness, I tried the following:

@Entity
public class Node extends Model<Node> {

    @PrimaryKey(autoIncrement = true)
    @Column(name = "_id")
    private int id;

    @Index(unique=true, name="uname")
    private String uniqueName;
    //...
}

However, Orman generates the following DDL for this: Executing: CREATE TABLE IF NOT EXISTS node (uname TEXT NOT NULL, _id INTEGER PRIMARY KEY AUTOINCREMENT)

Expected DDL is the following: CREATE TABLE IF NOT EXISTS node (uname TEXT UNIQUE, _id INTEGER PRIMARY KEY AUTOINCREMENT)

So, how do I set a column as UNIQUE but NULLable in SQLite (Android)?

[ahmetb]

Here are the queries executed on my side in log level set to trace (Log.setLevel(LoggingLevel.TRACE) before starting mapping session.):

142  [TRACE] Executing: CREATE TABLE node (_id INTEGER PRIMARY KEY AUTOINCREMENT, unique_name TEXT NOT NULL)
...
144  [TRACE] Executing: CREATE UNIQUE INDEX uname ON node (unique_name)

So at 144, you can see that unique index is created. Not all DBMSes support UNIQUE clause in CREATE TABLE query (e.g. in MySQL, you put CONSTRAINT colName UNIQUE at the end of field listing, for more see here) and we are trying to keep it close to SQL standards (although none of DBMSes follow SQL standards completely).

In other words, although the query executed is not exactly what you want, the unique index is created by the framework in the subsequent queries. It should also support null but unique constraint if I'm not wrong.

Please reopen the issue if there is something wrong.

[curioustechizen]

Okay, I see that the UNIQUE constraint has been created in the INDEX. Sorry for missing that. But I still cannot insert a NULL value to this column:

Trying to save instance with a null value on a @NotNull (not nullable) field: uname (in.curtech.trial.orman.entities.Node)

And of course this is expected, since the CREATE TABLE statement has added a NOT NULL constraint on uname column:

uname TEXT NOT NULL

Can I prevent this? Can I configure Orman to omit the NOT NULL constraint on a column that has been configured with a UNIQUE index?

[ahmetb]

It seems I have designed the framework in a way that indexed fields cannot be null because thats what I used to know. Now I pushed the fix (cd5eec5ca2798994aa4a70d246bfde6c0629e69a), can you please clone the repo and build a jar for yourself? Thanks!