Re: Referential Integrity and triggers

On Fri, 12 Jun 1998 09:30:46 -0400, "Lisa Lewis" <lewis_at_med.pitt.edu> wrote:

>I have two tables A and B. I don't want a user to be able to insert a
>record into table A unless A.field1 is in B.field3. B.field3 is not a PK
>and is not even unique. Is my only option to use triggers? If A.field1 not
>exist in B.field3.....reject the insert. Will this be a big hit on
>performance?

You are correct that foreign key constraints can only be made to a primary or a unique key, so I suspect that a trigger (of one sort or another) is the only solution to your problem. I can think of two approaches that you could take.

Approach 1: Create a trigger on table A that does a SELECT against table B to see if the correct field3 value exists. Be sure to index B.field3, otherwise this query will be very slow. I doubt that you will see a performance hit over that of a foreign key constraint, because an FK constraint has to do the same type of SELECT. A select is a select.

Approach 2: I'm not sure that I'd recommend this one. It's just an option that hit me tonight, so I'll pass it on.

1. Create a new table, call it table C. Make sure the primary key of C is field3.

	2.Write insert, update, and delete triggers
	on table B so that every time a new
	B.field3 value is inserted, that value
	gets propogated to C.field3.
	
	3. Create an FK from B.field3 to C.field3.