Thanks Charles for the nice Function.
I started this thread, but wasn't at my desk for the past few hours, I
just discovered a lot of differnt point of view regarding the Unique Id.
As I said previously, I am coming from FileMaker world and a bit of
PHP/MySQL. I always used in the last 10 years unique IDs for each table
in my different DB.
As a novice I used to rely on keys made out of a "unique" information
(National Health Insurance Nbr...) compounded keys, which I assumed at
the time would be unique (ie. Name-Surname-ZIP-Birthdate). But it always
one day or the other failed to work. Ladies would get married, some
people do not hold a National Health Nbr or are holdind their mother's
one (notice the same Nbr same Name same ZIP coumpounded key that fails
this time!), people would move away etc...
Therefore I turned myself to having an internal table holding for me the
last Id used for every table in the DB. This also failed sometimes (due
to Filemaker mostly) as when 2 people would hit the same validation at
the same time (believe it or not, this is Murffy's law).
Now I use 15 to 20 'randomly' generated keys using the alphabet plus all
figures. Benefits are :
Independant from datas in the record. Any change in the ifnormation will
not affect my key and then the integrity of my DB.
When exporting datas from one DB engine to any other engine, I only rely
on my datas to garantee the integrity of the DB, whichever is the DB engine.
So far, I never had any "duplicate" using that method. Chances are slim
but could happened.
I thought that there were some automatic function in SQLite, that would
generate these kind of keys. I'll therefor stick to my old habit or most
certainly use Charles function which can certainly garantee a better
uniqueness (is this correct English?) of each generated key. Thank you
all for your answers.
Youri
Charles Yeomans wrote:
On Oct 16, 2006, at 6:15 AM, Peter Bozek wrote:
On 10/16/06, Youri <[EMAIL PROTECTED]> wrote:
Regarding the Unique ID being an Integer, how can I trust an Integer to
be unique?
If this "rowid" is generated by SQLite, how will it be treated when
exporting datas out of the DB and back? How to maintain the "link"
between tables if I use these rowid for JOINS?
SQLite ensures that rowID is unique. However, your concerns are valid
and IMHO rowID has a limited usability as a primary key. In more
complex databases you should define your own primary key. There are
many ways how to do that:
- use Max function
- use separate table to store next sequence ID
- use build in support for defined sequences, if db supports it
(SQLite IMHO does not support sequences, but most other databases do)
- use UUID.
I used to use first two, but now qtry to switch to UUID, as it solves
quite a few problems (like export-import of data).
Related questions - do somebody have Declares for obtaining UUID from
MAC OS X and Windows?
For Mac OS X --
Function NewUUID() As String
soft declare function CFUUIDCreate Lib "Carbon.framework" (alloc as
Integer) as Integer
Const kCFAllocatorDefault = 0
dim uuid as Integer = CFUUIDCreate(kCFAllocatorDefault)
soft declare function CFUUIDCreateString Lib "Carbon.framework" (alloc
as Integer, uuid as Integer) as CFStringRef
dim uuidString as CFStringRef =
CFUUIDCreateString(kCFAllocatorDefault, uuid)
soft declare sub CFRelease Lib "Carbon.framework" (cf as Integer)
CFRelease uuid
return uuidString
End Function
Charles Yeomans
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
