Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: conditional foreign key constraint

Re: conditional foreign key constraint

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 14 Jan 2004 08:30:20 -0800
Message-ID: <1074097744.811435@yasure>


Cookie Monster wrote:

> Hi,
>
> I want to create a foreign key constraint which is only checked when a
> certain value in one of the field is set. such that I have a field called
> STATUS and when the value equals 1 then my foreign key is active on that
> row. I guess this is not really possible without a trigger but a trigger
> would take forever as I have thousands of rows. has anyone had something
> similar and has an idea of how I could perhaps do this.
>
> Thanks in advance
> cookie.

What you have hear screams in pain that you have a design issue. There is no possible relational design I can imagine, and I have a reasonably fertile imagination, that requires such a conditional constraint.

Why don't you tell us the business problem you are trying to solve and let us suggest the solution rather than telling us "your" solution and asking us to figure out how to do it.

The one thing I can tell you, for sure, is that you are not going to implement this without a trigger. But my instinct is that you shouldn't implement it at all.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Jan 14 2004 - 10:30:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US