Subject Re: [firebird-support] Procedures
Author Helen Borrie
At 02:18 PM 27/07/2004 +0000, you 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.

You have a serious relational design problem here. Your existing table
should be the one with the foreign key; it should point to the primary key
of a product master table, with referential integrity protecting the
existence of both the master key and the referring product key in your
existing table.

The idea of continually deleting and recreating the master keys won't fly.