>How do you get around the Uniqueness is Violated error? Is there
>anything more savvy than populating the unique code field with something
>like '####' and going out and looking for a duplicate?

Well, for primary keys, you should use a surrogate key: a key that has
no meaning. In other words, FOSTEDON01 would not be a good primary key
for my wife: when she married me and changed her name, the key would
either not match her name, or it would have to be changed in every place
that referred to it.

Another thing is that primary keys are used for joins, and the smaller the
key is, the faster the join. If you use an integer for a primary key, it's
only 4 bytes wide, but you can store 2 billion unique keys in it.

I use a separate table and fill in primary keys in order, but other people
use random key generators like SYS(3), and check for uniqueness before
they insert the key in the table.

As far as candidate keys go, I have handled this at times by trapping the
error, and at other times by USE AGAINing the table I was inserting into,
and searching in the second table for the value.

Your Mileage May Vary. :-)