Re: table constraint

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 26 Jun 2007 08:40:55 -0700
Message-ID: <1182872455.951963.287610@u2g2000hsc.googlegroups.com>

On Jun 26, 11:29 am, mlynch147 <martin.ly..._at_raytheon.co.uk> wrote:
> On Jun 26, 2:24 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > On Jun 25, 7:41 am, 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
> > Again, two more minutes into the future:
> > INSERT INTO
> > T1
> > SELECT /*+ ORDERED */
> > D.COLOUR,
> > D.DIRECTION,
> > NVL(MIN(T1.MY_DATE),D.MY_DATE) MY_DATE
> > FROM
> > (SELECT
> > 1 COLOUR,
> > 1 DIRECTION,
> > TO_DATE('25-JUN-2007 12:04:00','DD-MON-YYYY HH24:MI:SS') MY_DATE
> > FROM
> > DUAL D) D,
> > T1
> > WHERE
> > D.COLOUR=T1.COLOUR(+)
> > AND D.DIRECTION=T1.DIRECTION(+)
> > AND (D.MY_DATE-0.002083333)<=T1.MY_DATE(+)
> > AND (D.MY_DATE+0.002083333)>=T1.MY_DATE(+)
> > GROUP BY
> > D.COLOUR,
> > D.DIRECTION,
> > D.MY_DATE;
>
> > 1 row created.
>
> > Again, two more minutes into the future:
> > INSERT INTO
> > T1
> > SELECT /*+ ORDERED */
> > D.COLOUR,
> > D.DIRECTION,
> > NVL(MIN(T1.MY_DATE),D.MY_DATE) MY_DATE
> > FROM
> > (SELECT
> > 1 COLOUR,
> > 1 DIRECTION,
> > TO_DATE('25-JUN-2007 12:06:00','DD-MON-YYYY HH24:MI:SS') MY_DATE
> > FROM
> > DUAL D) D,
> > T1
> > WHERE
> > D.COLOUR=T1.COLOUR(+)
> > AND D.DIRECTION=T1.DIRECTION(+)
> > AND (D.MY_DATE-0.002083333)<=T1.MY_DATE(+)
> > AND (D.MY_DATE+0.002083333)>=T1.MY_DATE(+)
> > GROUP BY
> > D.COLOUR,
> > D.DIRECTION,
> > D.MY_DATE;
>
> > ORA-00001: unique constraint (ME.CON_T1_DATE) violated
>
> > If one session inserts a row but does not commit, and then a second
> > session inserts a row that would be within 3 minutes of the time used
> > by the first session, the second session will hang until the first
> > session commits or rolls back.
>
> > The DBMS Xplan for a successful insertion:
> > ---------------------------------------------------------------------------ญญ-------------------
> > | Id | Operation | Name | Starts | E-Rows | A-Rows
> > | A-Time | Buffers |
> > ---------------------------------------------------------------------------ญญ-------------------
> > | 1 | SORT GROUP BY NOSORT| | 1 | 1 | 1 |
> > 00:00:00.01 | 1 |
> > | 2 | NESTED LOOPS OUTER | | 1 | 1 | 1 |
> > 00:00:00.01 | 1 |
> > | 3 | FAST DUAL | | 1 | 1 | 1 |
> > 00:00:00.01 | 0 |
> > |* 4 | INDEX RANGE SCAN | IND_T1_DATE | 1 | 1 | 0 |
> > 00:00:00.01 | 1 |
> > ---------------------------------------------------------------------------ญญ-------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> > 4 - access("T1"."COLOUR"=1 AND "T1"."DIRECTION"=1 AND
> > "T1"."MY_DATE">=TO_DATE('2007-06-25 12:07:00', 'yyyy-mm-
> > dd hh24:mi:ss') AND
> > "T1"."MY_DATE"<=TO_DATE('2007-06-25 12:13:00', 'yyyy-mm-
> > dd hh24:mi:ss'))
>
> > The above will hopefully give you a couple ideas.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > - Show quoted text -
>
> Charles
> that looks really good thanks,
>
> just one question - how did you arrive at the value 0.002083333 that
> you use in your statements?
>
> Marty

Think of dates and times in the database as decimal numbers: Tomorrow - Today is 1 day. One hour would be 1/24 of a day. One minute would be 1/24/60 of a day. Three minutes would be 1/24/60*3 of a day: SELECT
  1/24/60*3
FROM
  DUAL;

---

0.002083333

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jun 26 2007 - 10:40:55 CDT