Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: table constraint
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
ROW 2
ROW 3
ROW 4
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
![]() |
![]() |