Re: Referential Integrity and triggers

>
>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?
>
>Thanks
>Lisa

Even though the strict definition of a foreign key says that it must reference the primary (ie unique) key of the table, Oracle does not seem to require that. On table 'A' create a foreign key references table B column field3. I always have to look it up in the manual when I do it by hand, so I can't give you the syntax; perhaps someone else can provide it.

There is very minimal impact on performance; if you create indexes on the columns involved from both tables, it will go that much faster.

I don't think you can use a constraint as was suggested because I seem to recall that a contraint can only reference columns in the current table and row, not columns in another table.

If you can't find the syntax, let me know and I will get it for you Monday.

Dan Received on Sat Jun 13 1998 - 00:00:00 CEST