Home » SQL & PL/SQL » SQL & PL/SQL » Constraints over multiple rows?
Constraints over multiple rows? [message #235128] Fri, 04 May 2007 02:37 Go to next message
colin_j
Messages: 2
Registered: May 2007
Location: Scotland
Junior Member
I have a table with a column that holds a percentage split of an item. I need to ensure that the total for the set of rows that refer to a single item add to 100%.

The rows are obviously created and updated separately but are commited as a whole.

Other than writing some code to check prior to the commit does anybody know of a way to do this in the database (I am worried about people manually updating rows and creating inconsistencies)?
Re: Constraints over multiple rows? [message #235135 is a reply to message #235128] Fri, 04 May 2007 02:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Although you *could* implement this through triggers, you have to take care of the dreaded mutating table problem (reading from the table you're updating.

I took a different approach on this one: materialized views.

My script:
CREATE TABLE mhe_foo( item NUMBER
                    , seqn NUMBER
                    , pctg NUMBER(5,2)
                    , CONSTRAINT pctg_check CHECK ( pctg BETWEEN 0 AND 100 )
                    )
/


CREATE MATERIALIZED VIEW mhe_mv( item
                               , sum_pctg
                               )
REFRESH FORCE ON COMMIT
AS
SELECT item
     , sum(pctg) sum_pctg
FROM   mhe_foo
GROUP  BY item
/
ALTER TABLE mhe_mv ADD CONSTRAINT sum_pctg_check CHECK (sum_pctg = 100)     
/

-- This will fail (120%)
INSERT INTO mhe_foo VALUES (1,1,20);
INSERT INTO mhe_foo VALUES (1,2,20);
INSERT INTO mhe_foo VALUES (1,3,20);
INSERT INTO mhe_foo VALUES (1,4,20);
INSERT INTO mhe_foo VALUES (1,5,20);
INSERT INTO mhe_foo VALUES (1,6,20);

COMMIT;

-- This will pass (100%)
INSERT INTO mhe_foo VALUES (1,1,20);
INSERT INTO mhe_foo VALUES (1,2,20);
INSERT INTO mhe_foo VALUES (1,3,20);
INSERT INTO mhe_foo VALUES (1,4,20);
INSERT INTO mhe_foo VALUES (1,5,20);

COMMIT;

-- This will fail (101%)
UPDATE mhe_foo
SET    pctg = 21
WHERE  item = 1
AND    seqn = 5
/
COMMIT
/

SELECT * FROM mhe_foo
/

SELECT * FROM mhe_mv
/

DROP MATERIALIZED VIEW mhe_mv
/

DROP TABLE mhe_foo
/

The principle is: you create a materialized view that refreshes on commit. Then you add a check constraint to this materialized view telling that the sum for each item has to be 100.

When I run my script I get this:
SQL> @orafaq

Table created.


Materialized view created.


Table altered.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.

COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (MHE.SUM_PCTG_CHECK) violated



1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.


1 row updated.

COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (MHE.SUM_PCTG_CHECK) violated



      ITEM       SEQN       PCTG
---------- ---------- ----------
         1          1         20
         1          2         20
         1          3         20
         1          4         20
         1          5         20


      ITEM   SUM_PCTG
---------- ----------
         1        100


Materialized view dropped.


Table dropped.


MHE
Re: Constraints over multiple rows? [message #235144 is a reply to message #235135] Fri, 04 May 2007 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good trick! Thumbs Up

Regards
Michel
Re: Constraints over multiple rows? [message #235170 is a reply to message #235135] Fri, 04 May 2007 04:07 Go to previous messageGo to next message
colin_j
Messages: 2
Registered: May 2007
Location: Scotland
Junior Member
Cheers that sounds like a great solution.
Re: Constraints over multiple rows? [message #278423 is a reply to message #235170] Sun, 04 November 2007 01:08 Go to previous messageGo to next message
kalyogi
Messages: 11
Registered: July 2007
Junior Member
gr8 explanation. Thanks
Re: Constraints over multiple rows? [message #278444 is a reply to message #278423] Sun, 04 November 2007 06:11 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've seen that idea before - probably here! It's very tricky and I can't help being impressed, but it also worries me a bit.

I don't like the idea of triggers any more (maybe a little less actually), but it's the MV Log I can't get around. It seems like an expensive IO overhead for what is a pretty simple requirement.

I wonder - and perhaps someone who has more experience can flesh this idea out - whether the same thing could be achieved with Index Types.

I have only the vaguest idea of how they work. My understanding is that every DML statement maintains a second table of your own design that can be used to resolve queries with special Index Type predicates against the base table. If this index-table were designed in much the same way as the MV solution, it might be possible to achieve the same result without an MV Log.

Interested to hear any ideas that would further or debunk this.

Ross Leishman
Previous Topic: creating multiple LIKE
Next Topic: Scheduled jobs not Running as per schedule
Goto Forum:
  


Current Time: Wed Dec 07 02:49:43 CST 2016

Total time taken to generate the page: 0.16792 seconds