Android Question SQLite & Unique

[luke2012]

Hi to all,
which is the best strategy to check if a value within a SQLite table is unique ?

1) Actually I execute a SQL query on the interested table looking for the value that I wish tu add.
If select return a record count = 0 than I assume that the value il unique within my table.

It's possibile to use the unique check of the SQLite db engine ?

 

[Erel]

You can use any feature of SQLite including creating a UNIQUE column.

 

[luke2012]

You can use any feature of SQLite including creating a UNIQUE column.

Ok. Assuming that how can I catch and handle the SQLite error (within B4A code) when I try tu put a non unique value into a unique column ?

 

[Erel]

Try
 SQL1.ExecuteNonQuery(...)
Catch
 ...
End Try

See http://www.b4x.com/android/forum/pages/results/?query=sql.begintransaction&page=1

 

[mc73]

You can avoid the rowCount, since this can produce a lot of records in a cursor, by simply using the count(*) statement.

Now, if you create a unique field, you can use an "insert or ignore" statement. If you don't wish to use a unique field, you can use a "where not exists" clause.

 

[luke2012]

Try
SQL1.ExecuteNonQuery(...)
Catch
...
End Try

See http://www.b4x.com/android/forum/pages/results/?query=sql.begintransaction&page=1

that's ok!
But how I can known the error code that I got from the SQLite engine ?

 

[luke2012]

You can avoid the rowCount, since this can produce a lot of records in a cursor, by simply using the count(*) statement.

Now, if you create a unique field, you can use an "insert or ignore" statement. If you don't wish to use a unique field, you can use a "where not exists" clause.

Thaks for your reply!
count(*) count all records in the table (right?).
I need to known if a specific record already exists.

 

[eps]

If you do something like select count(*) from <table> where <identifying attribute> = supplied attribute

and this returns 1, then you know it already exists. If it returns 0, then you can create.

 

[luke2012]

I think that both solution are valid.
I think that the @Erel solution is faster on large databases (use SQLite engine).

The @eps solution is more smart for me but probably don't perform as @Erel solution.

 

[eps]

There's almost always more than one way to do things, the choice is yours.