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: table constraint

Re: table constraint

From: mlynch147 <martin.lynch_at_raytheon.co.uk>
Date: Mon, 25 Jun 2007 07:17:15 -0700
Message-ID: <1182781035.387761.197720@c77g2000hse.googlegroups.com>


Hi Rob

I hope i havent missed something obvious here but the examples you have used only seem to give violations when the two dates are identical.... am i right?

I would lke this to happen... my table 'duplicate' will have 10 columns and i need to have a constraint on 3 of those, ie COLOUR, DIRECTION and DATE. If i have the following row in my table i would like to reject/accept the data after....

CURRENT TABLE STATUS - 1 ROW (below)
colour=1
direction=1
date=25thJune2007,15:13:00

New Rows and expected results....

ROW 1



colour=1
direction=2
date=25thJune2007,15:13:00
RESULT=ACCEPT as DIRECTION is different

ROW 2



colour=1
direction=1
date=25thJune2007,15:13:01
REULT=UNIQUE CONSTRAINT - colour/direction match and date/time is inside 3 minute window

ROW 3



colour=1
direction=1
date=25thJune2007,15:12:58
REULT=UNIQUE CONSTRAINT - colour/direction match and date/time is inside 3 minute window

ROW 4



colour=1
direction=1
date=25thJune2007,15:13:04
REULT=ACCEPT - colour/direction match and date/time is outside 3 minute window

Will your solution do this?

Thanks for your help
Marty

rwij..._at_gmail.com wrote:

> On 25 jun, 13:41, mlynch147 <martin.ly..._at_raytheon.co.uk> wrote:
> > Hi
> >
> > can someone tell me if this is possible.
> >
> > I would like to put a unique constraint on three columns of a table.
> > one of the columns is a date/time field but i need the constraint to
> > be +/- 3 mins of any data already in the table....
> >
> > eg, constraint on colour, direction, date columns
> >
> > colour = 1
> > direction = 1
> > date = June 25th 2007, 12pm
> >
> > If someone tries to enter another row with the values 1,1 and 12:00
> > +/- 3 minutes, a unique constaint violation will happen....
> >
> > is this possible?
> >
> > thanks for any help you can provide....
> >
> > Marty
>
>
> It is done like this:
>
> SQL> create table mytable
> 2 as
> 3 select 1 colour, 1 direction,
> to_date('25062007120000','ddmmyyyyhh24miss') mydate from dual
> 4 /
>
> Tabel is aangemaakt.
>
> SQL> create unique index i1 on
> mytable(colour,direction,trunc(mydate),trunc(to_number(to_char(mydate,'sssss'))/
> 180))
> 2 /
>
> Index is aangemaakt.
>
> SQL> insert into mytable values
> (1,1,to_date('25062007120200','ddmmyyyyhh24miss'))
> 2 /
> insert into mytable values
> (1,1,to_date('25062007120200','ddmmyyyyhh24miss'))
> *
> FOUT in regel 1:
> .ORA-00001: unique constraint (RWK.I1) violated
>
>
> SQL> insert into mytable values
> (1,1,to_date('25062007120300','ddmmyyyyhh24miss'))
> 2 /
>
> 1 rij is aangemaakt.
>
> SQL> insert into mytable values
> (1,1,to_date('25062007120400','ddmmyyyyhh24miss'))
> 2 /
> insert into mytable values
> (1,1,to_date('25062007120400','ddmmyyyyhh24miss'))
> *
> FOUT in regel 1:
> .ORA-00001: unique constraint (RWK.I1) violated
>
>
> SQL> insert into mytable values
> (1,1,to_date('26062007120400','ddmmyyyyhh24miss'))
> 2 /
>
> 1 rij is aangemaakt.
>
> SQL> insert into mytable values
> (1,2,to_date('25062007120300','ddmmyyyyhh24miss'))
> 2 /
>
> 1 rij is aangemaakt.
>
> Regards,
> Rob.
Received on Mon Jun 25 2007 - 09:17:15 CDT

Original text of this message

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