Re: Help with SQL constraint

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 15 Feb 2007 16:48:07 -0800
Message-ID: <1171586888.256453@bubbleator.drizzle.com>

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
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Feb 15 2007 - 18:48:07 CST