Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: table constraint
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