Re: Help with Oracle constrant on two tables problem

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: Mon, 18 Feb 2008 00:42:18 -0800 (PST)
Message-ID: <68bdfe71-57ab-4040-a316-c56acf22ab62@v3g2000hsc.googlegroups.com>

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?
>
> Thank you
>
> Jon

Hello Jon,

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.

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

www.tessella.com Registered in England No. 1466429

SQL>
SQL>
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>
SQL>
SQL>
SQL> CREATE TABLE tblValid(id NUMBER,id1 NUMBER, PRIMARY KEY(id,id1))

ORGANIZATION INDEX; Table created.

SQL>
SQL>
SQL>
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>
SQL>
SQL>
SQL>
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.

SQL>
SQL>
SQL>
SQL>
SQL> --Attempt 1 row in transaction - should be rejected as 4 required
SQL> INSERT INTO tblT2(id,id1,description)

  2 VALUES(100,1,'description1');

1 row created.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (PFIZERCOMET.DI_CORRECTMETHODSETCOUNT) violated
- parent key not found

SQL>
SQL>
SQL>
SQL> --Attempt 2 rows in transaction - should be rejected as 4

required
SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description1');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description2');

1 row created.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (PFIZERCOMET.DI_CORRECTMETHODSETCOUNT) violated
- parent key not found

SQL>
SQL>
SQL> --Attempt 3 rows in transaction - should be rejected as 4

required
SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description1');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description2');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description3');

1 row created.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (PFIZERCOMET.DI_CORRECTMETHODSETCOUNT) violated
- parent key not found

SQL>
SQL>
SQL>
SQL> --Attempt 4 rows in transaction - should commit
SQL> INSERT INTO tblT2(id,id1,description)

  2 VALUES(100,1,'description1');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description2');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description3');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(100,1,'description4');

1 row created.

SQL> COMMIT; Commit complete.

SQL>
SQL>
SQL> --Attempt another 4 rows in transaction - should commit
SQL> INSERT INTO tblT2(id,id1,description)

  2 VALUES(101,1,'description1');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(101,1,'description2');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(101,1,'description3');

1 row created.

SQL> INSERT INTO tblT2(id,id1,description)   2 VALUES(101,1,'description4');

1 row created.

SQL> COMMIT; Commit complete.

SQL>
SQL> SELECT *
  2 FROM tblT1;

       ID1
CHECKCOUNT

---

---

         1
4

         2
5

         3
10

         6
14

         9
5

SQL> SELECT *
  2 FROM tblT2;

        ID ID1
DESCRIPTION
---------- ----------

---

       100 1
description1

       100 1
description2

       100 1
description3

       100 1
description4

       101 1
description1

       101 1
description2

       101 1
description3

       101 1
description4

8 rows selected.

SQL> SELECT *
  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 -
Production
CORE 10.1.0.4.0
Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 -
Production

SQL> SPOOL OFF Received on Mon Feb 18 2008 - 02:42:18 CST