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 06:24:52 -0700
Message-ID: <1182864292.048118.207020@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




Let's try an experiment to see if this is possible without the cost of
a context switch.



A basic table, note that I did not set up a primary key, as I suspect
that there are other columns that would be included in the table that
might actually be the primary key:


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);


Now, insert the first row:


INSERT INTO



  T1


VALUES (



  1,


  1,


  TO_DATE('25-JUN-2007 12:00:00','DD-MON-YYYY HH24:MI:SS'));


A quick test to make certain that the syntax is correct:
SELECT



  1 COLOUR,



  1 DIRECTION,



  TO_DATE('25-JUN-2007 11:59:00','DD-MON-YYYY HH24:MI:SS') MY_DATE

FROM



  DUAL D;


    COLOUR  DIRECTION	    MY_DATE


========== ========== ====================
	 1	    1 25-JUN-2007 11:59:00





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:
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;

    COLOUR  DIRECTION	    MY_DATE
========== ========== ====================
	 1	    1 25-JUN-2007 12:00:00





The ORDERED hint is just to keep 10G from trying to do something other
than what was expected with the join.



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



Try again:


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:57: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



And again:


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:56:45','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.



Now test the postive side:


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:02: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



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.
Received on Tue Jun 26 2007 - 08:24:52 CDT












Original text of this message













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