On Sun, Aug 14, 2011 at 00:16, squidsrus85 <squidsrus85@...> wrote:

> Is there a better method of unique identifiers than just the AutoInc?

I prefer unique integers as primary keys. Even when I could make a
compound key out of two or more columns, I still create one integer pk
column. It helps to be consistent across all tables. The equivalent to
AutoInc would be a trigger in your table filling the pk column from a
generator unless the column is not null (in case you supply pk values
on the client).

I use this as a template when creating new tables (use search and
replace on %tableprefix% and %tablename%):
http://pastebin.com/RYzx9ySk
(Keep in mind that maximum identifier length in Firebird is something
like 31 characters -- can't remember it right now)

Then make sure you add this to IB_Connection.GeneratorLinks (or
IB_Query.GeneratorLinks):
%tableprefix%_id_%tablename%=gen_%tableprefix%_id_%tablename%
(e.g. acc_id_account=gen_acc_id_account)

With GeneratorLinks IBO will fetch a unique pk value for you whenever
you insert a new record. And in case you insert from another
application (e.g. FlameRobin, IB_SQL etc) then your trigger will fill
in the pk value if it wasn't specified in the insert statement.

In my template you'll also note that I use named constraints: If you
don't name them then Firebird will do it for you, but the names might
be different in different databases. This makes it more difficult to
drop or change them later (e.g. in an update-script you deploy to your
customers).

HTH,
Markus