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: <fitzjarrell_at_cox.net>
Date: Mon, 25 Jun 2007 06:23:53 -0700
Message-ID: <1182777833.004686.102920@u2g2000hsc.googlegroups.com>


Comments embedded.
On Jun 25, 8:08 am, 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,'ssss­s'))/
> 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
>

As it should be.

> SQL> insert into mytable values
> (1,1,to_date('25062007120300','ddmmyyyyhh24miss'))
> 2 /
>
> 1 rij is aangemaakt.

No, according to the OP this record should also be discarded as violating the constraint.

>
> 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.
>

This record should also violate the constraint. I think you have more work ahead of you to prove your claim.

> Regards,
> Rob.- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Mon Jun 25 2007 - 08:23:53 CDT

Original text of this message

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