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: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 26 Jun 2007 20:51:58 -0700
Message-ID: <1182916318.519273.216720@g4g2000hsf.googlegroups.com>


On Jun 26, 9:51 pm, "Bob Jones" <e..._at_me.not> wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
>
> news:1182864292.048118.207020_at_o61g2000hsh.googlegroups.com...
> > 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
>
> > CREATE TABLE T1(
> > COLOUR NUMBER(10),
> > DIRECTION NUMBER(10),
> > MY_DATE DATE);
>
> > Then, create an index on the three columns of interest:
> > CREATE INDEX IND_T1_DATE ON T1(
> > COLOUR,
> > DIRECTION,
> > MY_DATE);
>
> > Make certain that exact matches of COLOUR, DIRECTION, and MY_DATE are
> > prohibited:
> > ALTER TABLE T1 ADD CONSTRAINT CON_T1_DATE UNIQUE
> > (COLOUR,DIRECTION,MY_DATE);
> > If we were to insert the above into the T1 table, we would want a
> > check constraint violation, so we would somehow need to change the
> > 11:59 to 12:00 to generate a check constraint violation. For example:
>
> > If we then take the above and make it into an INSERT statement:
> > 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 11:59: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;
>
> > ERROR at line 1:
> > ORA-00001: unique constraint (ME.CON_T1_DATE) violated
>
>
> > The above will hopefully give you a couple ideas.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> While this solution is thoughtful, it gives a unique meaning to the word
> "unique".

I am not sure if that is a good thing or not...

Here is another way to solve the problem, but it is a bit less clean than the first method:

INSERT INTO
  T1
VALUES (
  1,
  1,
  TO_DATE('25-JUN-2007 12:00:00','DD-MON-YYYY HH24:MI:SS')); INSERT INTO
  T1
SELECT /*+ ORDERED */
  1 COLOUR,
  1 DIRECTION,
  NVL(C.MY_DATE,TO_DATE('25-JUN-2007 12:03:01','DD-MON-YYYY HH24:MI:SS')) MY_DATE
FROM
  DUAL D,
  (SELECT
    MIN(MY_DATE) MY_DATE
  FROM
    T1
  WHERE
    COLOUR=1
    AND DIRECTION=1
    AND MY_DATE BETWEEN TO_DATE('25-JUN-2007 12:03:01','DD-MON-YYYY HH24:MI:SS')-0.002083333 AND TO_DATE('25-JUN-2007 12:03:01','DD-MON- YYYY HH24:MI:SS')+0.002083333) C; 1 row created.

INSERT INTO
  T1
SELECT /*+ ORDERED */
  1 COLOUR,
  1 DIRECTION,
  NVL(C.MY_DATE,TO_DATE('25-JUN-2007 12:06:02','DD-MON-YYYY HH24:MI:SS')) MY_DATE
FROM
  DUAL D,
  (SELECT
    MIN(MY_DATE) MY_DATE
  FROM
    T1
  WHERE
    COLOUR=1
    AND DIRECTION=1
    AND MY_DATE BETWEEN TO_DATE('25-JUN-2007 12:06:02','DD-MON-YYYY HH24:MI:SS')-0.002083333 AND TO_DATE('25-JUN-2007 12:06:02','DD-MON- YYYY HH24:MI:SS')+0.002083333) C; 1 row created.

INSERT INTO
  T1
SELECT /*+ ORDERED */
  1 COLOUR,
  1 DIRECTION,
  NVL(C.MY_DATE,TO_DATE('25-JUN-2007 12:08:00','DD-MON-YYYY HH24:MI:SS')) MY_DATE
FROM
  DUAL D,
  (SELECT
    MIN(MY_DATE) MY_DATE
  FROM
    T1
  WHERE
    COLOUR=1
    AND DIRECTION=1
    AND MY_DATE BETWEEN TO_DATE('25-JUN-2007 12:08:00','DD-MON-YYYY HH24:MI:SS')-0.002083333 AND TO_DATE('25-JUN-2007 12:08:00','DD-MON- YYYY HH24:MI:SS')+0.002083333) C; ERROR at line 1:
ORA-00001: unique constraint (ME.CON_T1_DATE) violated

Definitely, test the different approaches to see what works the best.

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

Original text of this message

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