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: <rwijk72_at_gmail.com>
Date: Mon, 25 Jun 2007 13:08:38 -0000
Message-ID: <1182776918.981856.16900@p77g2000hsh.googlegroups.com>


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 - 08:08:38 CDT

Original text of this message

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