Re: table constraint

"Charles Hooper" <hooperc2000_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
>
> 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.
>

While this solution is thoughtful, it gives a unique meaning to the word "unique". Received on Tue Jun 26 2007 - 20:51:38 CDT