You can create a new column in your products table and turn this
column its primary key, populated with a BEFORE INSERT trigger and a
generator.

This way, you can use this new column as the foreign key of the new table.

--- In firebird-support@yahoogroups.com, "lartz001" <rob@c...> wrote:
> I have a table with product codes and other data, the product codes
> (because how the table works) can be repeated multiple times so i am
> unable to make the field unique for obvious reasons.
>
> But I have another table which i want to create a foreign key to
> relate to the product code. It requires the reference key to be
> unique.... so how I thought about doing it would be to have another
> table which simply had one field full of the unique product codes.
>
> And then have a procedure which would run on request to delete all
> product codes from this new table and then it would "recompile" the
> unique product codes into this table. so basically selecting unique
> product codes from the main table and then inserting them into the
> new table. The question is how would I write a procedure to do this?
> as i have no experience in writing procedures, so if someone could
> assist me in writing this then I would appreciate it or if anyone can
> come up with a better solution.
>
> Cheers