Re: Help with Oracle constrant on two tables problem

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 Received on Mon Feb 18 2008 - 13:10:09 CST