Question about SQlite

[Martin Francom]

When the constraint UNIQUE  is add to a field... What exactly does it do? 
ie:

Code Select Expand


   t1 = "Create Table Customers (Company collate NoCase,  NCPDP, UserCount, FName, LName, Add1, Add2, " & _
        "City, State, Zip, Email, Phone, Fax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note, " & _
        "[b]Unique[/b](Company, NCPDP, FName, LName, State, AExp, Lexp, OExp));"   

   slExe "Begin"  'This starts the transaction
      slExe t1
      slExe "CREATE INDEX NCPDP ON Customers (NCPDP)"
      slExe "CREATE INDEX Company ON Customers (Company)"
      slExe "CREATE INDEX AExp ON Customers (AExp)"
      slExe "CREATE INDEX LExp ON Customers (LExp)"
      slExe "CREATE INDEX OExp ON Customers (OExp)"
      slExe "CREATE INDEX State ON Customers (State)"
      slEXE "CREATE INDEX Name ON Customers (LName, FName)"
   slExe "End"
 

Does it mean that SQLite will make the field enterred unique regardles of how many duplicate names are entered?
Or, does it mean that SQLite will prevent a duplicate name to being saved to the database?

[Rolf Brandt]

Hello Marty,

When the constraint UNIQUE  is add to a field... What exactly does it do?

The SQLite documentatation says: http://www.sqlite.org/lang_createtable.html

The UNIQUE constraint causes an unique index to be created on the specified columns. All NULL values are considered different from each other and from all other values for the purpose of determining uniqueness, hence a UNIQUE column may contain multiple entries with the value of NULL.

Does it mean that SQLite will make the field enterred unique regardles of how many duplicate names are entered?
Or, does it mean that SQLite will prevent a duplicate name to being saved to the database?

Yes, SQLite will prevent a duplicate name to being saved to the database. This will cause the error 19 See SQLitenig documentation in White Papers --> SQLite Error Codes:
19 = Abort due to constraint violation (Normally caused by trying to add a non unique key to an index during an Insert or Update)


Rolf

[Martin Francom]

Thanks Rolf,  now I understand why making the field unique was not fixing my problem with browsing the rows (records) was not working.  I found that adding a unique number at the end ot the field that I want to use as an index made it so the browse routine would stop at each row (record) and not skip over records. 

I found if I had not added a uniqueness to the field (ei city) and had a number of "Seattle"'s  then the browse would find the first "Seattle" and when I moved to the next record it would skip all the other "Seattle" records and move to the next city different from Seattle.
By adding a unique number to each indexed field, which make the field look unique to SQLite,  I am able to walk through the database alphabetically one record record at a time.  In a manor similar to Cheatah's GetPrev/GetNext methods. 
There may be a better way to do this, but I haven't found it.  This at least works even if it's not the proper way to do it.
If someone can modify the program to change it use a more proper SQL method of doing this, I would love to see how it's done.  I know I have lot's to learn about  programming with SQLite.

The next thing I need to learn is how to use two fields to build an index.  Fred gave me some suggestions but I haven't been successful in getting it to work.  I keep getting SQL errors and the program crashing.  Do you have the time to take a look and see what I am doing wrong?

[Rolf Brandt]

Of course, I can look at it.

Rolf

[Martin Francom]

Of course, I can look at it.

Rolf

Rolf,
   Thanks that will be a big help.  What I wold like help on is to add an Index for the Name fields.  I have split the original name field into a FName and LName fields. So what I need help doing is "Creating" an Index based on the LName and FName fields. I have made several attemps but always run into a problem after trying to create new indexes. 

   The database would need to be restructure and the new Index added.   I will post the current project.  The version I am posting  compiles and functions correctly.  So, you will be able to start with a project that is working.   I thought this would be better than giving you the broken code.

[Rolf Brandt]

OK, that sounds good.

[Rolf Brandt]

Creating an Index from more than one field (SQLite documentation):

The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key.

Example:
slExe "CREATE INDEX CustomerName ON Customers (LName, FName);"