Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Help with SQL constraint

From: DA Morgan <>
Date: Fri, 16 Feb 2007 08:24:25 -0800
Message-ID: <> wrote:
> On 16 fév, 01:48, DA Morgan <> wrote:

>> dean wrote:
>>> On Feb 15, 11:41 am, DA Morgan <> 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)constraintsuch 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).
>> 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.

> I dont have the slightest idea of the OP requirements, Daniel,
> but consider this one :
> You are monitoring N crossroads. On each crossroad, there is a
> sensor that detects whether a car is passing through (actually, we
> need four, but well... :-)
> A sensor sends a message to the monitoring application whenever
> a car is seen.
> You have to count and keep an history of the number of cars passing
> through each crossroad within period of times of - say - one hour.
> Traffic is very intense.
> How would you model this ?

You store all hits received even when they are dups. Then, in your report, or using a materialized view, you formulate an accurate report based on that data.

All hits received from all sensors are equally valid. It is the job of reporting to turn it into information.

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Feb 16 2007 - 10:24:25 CST

Original text of this message