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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL constraint

Re: Help with SQL constraint

From: dean <deanbrown3d_at_yahoo.com>
Date: 15 Feb 2007 13:58:17 -0800
Message-ID: <1171576697.885748.89750@q2g2000cwa.googlegroups.com>


On Feb 15, 4:54 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
> On Feb 15, 11:41 am, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
>
>
> > Arto Viitanen wrote:
> > > dean wrote:
> > >> Hello all,
>
> > >> A table T has 2 fields, one (L) holding letters 'Y' and 'N', and one
> > >> (X) holding numbers. Is there a (non trigger) constraint such that for
> > >> L='N' (and only this letter) the numbers must be unique? Records
> > >> where L='Y' do not have to be unique.
>
> > >> (I need to join another table to the L='N' group of records, and the
> > >> join must be key-preserved).
>
> > >> Cheers,
>
> > >> Dean
>
> > > Why not divide the table to two tables, one containing to constraint
> > > (with UNIQUE) and the other not. Then make a view using UNION for the
> > > original table.
>
> > Report writers will love you.
>
> > The OP still hasn't answered the question: "What's wrong with triggers."
>
> > Why break an application to solve a problem we still don't understand?
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> I have answered twice now. Because you have not yet understood the
> answer does not mean one was not given. The reason is this: To create
> an updatable view between two tables, Oracle must create what is known
> as a key-preserved join between them. If I have another table T2
> joined to my table above (T), and the primary key on T2 is field K1,
> and the foreign key to T is F1, the Oracle has to KNOW that for every
> record in T2, there can be ONE and ONLY ONE record when joined to T.
> Oracle does not look at the data to see if this is the case (I wish it
> did), instead it must first pre-calculate if T2 can ever logically be
> updatable based on the fields of T to which it is being joined. So I'm
> trying to convince Oracle that there is indeed one and only one record
> in T for any particular value of K1 in T2 - basically with a unique
> constraint.
>
> HTH
> Dean- Hide quoted text -
>
> - Show quoted text -

Ok having read that back to myself, I'm pretty sure its still as clear as mud, so maybe I'll create a test case example and post it here soon.... Received on Thu Feb 15 2007 - 15:58:17 CST

Original text of this message

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