At 10:18 AM 7/27/2004, lartz001 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.

I'd suggest looking in your "table with product codes and other data"
- you may find other information that depends on the product code
and should be in a product codes table.

>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.

I would create a table with a primary key of product code and include
in it any other data that is functionally dependent* on the product
code. Populate that table with an insert driven by a select distinct.
Set up foreign key constraints between the product code in the new
table and the product codes in the existing tables.

For the table that now holds the "product codes and other data", add
a trigger that fires before insert and before update. Have the trigger
check the product codes table for the new product code value and store
it if it doesn't exist.


Regards,

Ann

*Functional dependency is relational-geek speak. If you don't know what it
means, you should.