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 22:54:32 -0800
Message-ID: <1171608872.240530.104810@p10g2000cwp.googlegroups.com>


On Feb 15, 7:48 pm, DA Morgan <damor..._at_psoug.org> wrote:
> dean 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
>
> I have reread this entire thread and not once before did you, in my
> opinion, state what you just said.
>
> So, essentially, you want to NOT have unique data but convince Oracle,
> by some hocus-pocus magic trick, that the data is unique.
>
> As I presume you intend to put this into production there is no more
> help available from me. YOYO. There is one and only one solution ...
> correctly model your business requirement.
> --
> 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 -

Hocus-pocus trick, no. Magic, maybe. It is unique data for one particular value of the stated field. Can you not imagine how useful it could be to have this scenario sometimes: Field 1, value'A' can have in Field 2 a range of numbers 1-5. 'B' can have 6-10. 'C' could have 11-50?

> There is one and only one solution ... correctly model your business requirement.

Why do you think that productions systems are always so easily changed? How many companies have you consulted for that could just clean-sweep their database design and not go out of business? I work with a set of railroad companies that are still using 1960s technologies on proprietory mainframe systems, maybe I should suggest they upgrade some time!

The table in question is at the heart of a large system. I'm running some update queries on the table, and I think if I could join it properly to another table I could get the update to run faster. It would be an updatable join rather than an 'exists' query. Received on Fri Feb 16 2007 - 00:54:32 CST

Original text of this message

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