Re: Help with Oracle constrant on two tables problem

On Feb 18, 7:10 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 18.02.2008 09:42, Michael O'Shea wrote:
>
>
>
>
>
> > On Feb 16, 2:17 pm, jharbo..._at_googlemail.com wrote:
> >> This is my first post to Google groups. I am write with a new program
> >> I am writing using Oracle XE on Windows XP home.
>
> >> Here it is.
>
> >> I have a table called "table1" that has the following in it
>
> >> id1     checkCount
> >>  1         4
> >>  2         5
> >>  3         10
> >>  6         14
> >>  9         5
>
> >> "table2" has the following
>
> >> id1      Description
> >>  1       id1description1
> >>  1       id1description2
> >>  1       id1description3
> >>  1       id1description4
> >>  2       id2description1
> >>  2       id2description2
> >>  2       id2description3
> >>  2       id2description4
> >>  2       id2description5
>
> >> checkCount is the number of rows in "table2" that must have the same
> >> id1 (there are 4 rows with id1's with value 1, there are 5 rows with
> >> id1's with value 2 as in "table1")
>
> >> I need to guarentee applications don't write to "table2" with an
> >> incorrect number of rows (for example to write 1, 2, 3, 5, 6, 7, 8,
> >> etc number of rows with id1. it should have 4 rows and always 4).
>
> >> Can this be done with oracle XE with constrants?
> > Using your dataset I have demonstrated a FK NN retrofit approach for
> > you below. I have assumed you omitted to state that the lookup table
> > defining the number of rows that should exist in the parent table
> > grouped by id may be referenced more than once.
> > SQL> CREATE TABLE tblT1(id1 NUMBER, checkCount NUMBER);
>
> > Table created.
>
> > SQL> INSERT INTO tblT1(id1,checkCount)
> >   2   VALUES(1,4);
>
> > 1 row created.
>
> > SQL> INSERT INTO tblT1(id1,checkCount)
> >   2   VALUES(2,5);
>
> > 1 row created.
>
> > SQL> INSERT INTO tblT1(id1,checkCount)
> >   2   VALUES(3,10);
>
> > 1 row created.
>
> > SQL> INSERT INTO tblT1(id1,checkCount)
> >   2   VALUES(6,14);
>
> > 1 row created.
>
> > SQL> INSERT INTO tblT1(id1,checkCount)
> >   2   VALUES(9,5);
>
> > 1 row created.
>
> > SQL>
> > SQL>
> > SQL> CREATE TABLE tblT2(id NUMBER, id1 NUMBER, description
> > VARCHAR2(15));
>
> > Table created.
>
> > SQL> CREATE TABLE tblValid(id NUMBER,id1 NUMBER, PRIMARY KEY(id,id1))
> > ORGANIZATION INDEX;
>
> > Table created.
>
> > SQL> ALTER TABLE tblT2
> >   2   ADD CONSTRAINT DI_CorrectMethodSetCount
> >   3    FOREIGN KEY(id,id1)
> >   4     REFERENCES tblValid(id,id1)
> >   5     INITIALLY DEFERRED DEFERRABLE;
>
> > Table altered.
>
> > SQL> CREATE OR REPLACE TRIGGER trgT2
> >   2   BEFORE INSERT ON tblT2
> >   3    FOR EACH ROW
> >   4  DECLARE
> >   5  BEGIN
> >   6   DELETE FROM tblValid
> >   7    WHERE id1 = :NEW.id1
> >   8      AND id = :NEW.id;
> >   9
> >  10   INSERT INTO tblValid(id,id1)
> >  11    SELECT tblt2.id,tblt1.id1
> >  12     FROM tblT1, tblT2
> >  13      WHERE tblT1.id1 = tblT2.id1
> >  14       AND tblT1.id1 = :NEW.id1
> >  15       GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id
> >  16        HAVING COUNT(*) = tblT1.checkCount-1;
> >  17  END;
> >  18  /
>
> > Trigger created.
>
> One question and one remark: is there a particular reason that you
> choose DELETE and INSERT over UPDATE and INSERT in your trigger?
>
> As far as I can see your code does not prevent errors introduced through
> UPDATE and DELETE.
>
> Kind regards
>
>         robert- Hide quoted text -
>
> - Show quoted text -

Robert, Hi. Comments in-line.

> One question and one remark: is there a particular reason that you
> choose DELETE and INSERT over UPDATE and INSERT in your trigger?

Is is a not null foreign key constraint and the constraint FK "existence" requires a delete first so the transaction fails if the insert inserts no rows (ie. if no rows are returned by the select because of the "HAVING COUNT(*) = tblT1.checkCount-1").

> As far as I can see your code does not prevent errors introduced through
> UPDATE and DELETE.

Err ... umm ... you're right. It's a trivial modification though have the trigger fire on an insert/update. Thanks for pointing this out.

Regards

Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301