Re: table constraint

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
>
> 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.- 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 Received on Tue Jun 26 2007 - 10:29:41 CDT