Android Question SQLite & Unique

luke2012 · Oct 7, 2013

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 · Oct 7, 2013

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

luke2012 · Oct 9, 2013

Erel said:
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 · Oct 9, 2013

B4X:

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

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

mc73 · Oct 9, 2013

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 · Oct 9, 2013

Erel said:
B4X:

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 · Oct 9, 2013

mc73 said:
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 · Oct 9, 2013

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 · Oct 9, 2013

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 · Oct 9, 2013

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

Android Question SQLite & Unique

luke2012

Well-Known Member

Erel

B4X founder

luke2012

Well-Known Member

Erel

B4X founder

mc73

Well-Known Member

luke2012

Well-Known Member

luke2012

Well-Known Member

eps

Expert

luke2012

Well-Known Member

eps

Expert

Similar Threads

Android Question SQLite & Unique

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency