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

On Feb 16, 9:17*am, jharbo... (AT) googlemail (DOT) com wrote:
I can think of a way to accomplish this but I make no guarentees that
this method will be acceptable for your needs.

First as I understand the problem a transaction to insert rows into
table2 must insert exactly the number of rows as specified by table1
based on the value of the ID column.

This will only work if the table owner username is not used by the
application, that is, the application username must not have insert
on table2. Rename table2 and replace it with a view for selects.
Place an instead of trigger on the view that does not allow insert.
Code a package to update the renamed table. The package will check
the ID and store rows submitted for insert into a pl/sql table (array)
then when the proper number of rows have been submitted the
application indicates it has submitted all the data to be inserted.
The package checks the count and then performs the insert into the
renamed table (not the view) or issues an error.

Otherwise your application logic would have to do a count(*) of the
inserted rows (would require adding a transaction Id or the same date/
time to each row in the transaction to identify them) prior to issuing
a commit. If only one program performs inserts into Table2 then that
may be OK, but if you want to guarentee the relationship you need to
force the use of logic designed to maintain the relationship.

What happens when the value of the rows in table2 identified in table1
needs to change?
What about deletes from table2?

HTH -- Mark D Powell --

On Feb 16, 9:17*am, jharbo... (AT) googlemail (DOT) com wrote:
I can think of a way to accomplish this but I make no guarentees that
this method will be acceptable for your needs.

First as I understand the problem a transaction to insert rows into
table2 must insert exactly the number of rows as specified by table1
based on the value of the ID column.

This will only work if the table owner username is not used by the
application, that is, the application username must not have insert
on table2. Rename table2 and replace it with a view for selects.
Place an instead of trigger on the view that does not allow insert.
Code a package to update the renamed table. The package will check
the ID and store rows submitted for insert into a pl/sql table (array)
then when the proper number of rows have been submitted the
application indicates it has submitted all the data to be inserted.
The package checks the count and then performs the insert into the
renamed table (not the view) or issues an error.

Otherwise your application logic would have to do a count(*) of the
inserted rows (would require adding a transaction Id or the same date/
time to each row in the transaction to identify them) prior to issuing
a commit. If only one program performs inserts into Table2 then that
may be OK, but if you want to guarentee the relationship you need to
force the use of logic designed to maintain the relationship.

What happens when the value of the rows in table2 identified in table1
needs to change?
What about deletes from table2?

HTH -- Mark D Powell --

On Feb 16, 9:17*am, jharbo... (AT) googlemail (DOT) com wrote:
I can think of a way to accomplish this but I make no guarentees that
this method will be acceptable for your needs.

First as I understand the problem a transaction to insert rows into
table2 must insert exactly the number of rows as specified by table1
based on the value of the ID column.

This will only work if the table owner username is not used by the
application, that is, the application username must not have insert
on table2. Rename table2 and replace it with a view for selects.
Place an instead of trigger on the view that does not allow insert.
Code a package to update the renamed table. The package will check
the ID and store rows submitted for insert into a pl/sql table (array)
then when the proper number of rows have been submitted the
application indicates it has submitted all the data to be inserted.
The package checks the count and then performs the insert into the
renamed table (not the view) or issues an error.

Otherwise your application logic would have to do a count(*) of the
inserted rows (would require adding a transaction Id or the same date/
time to each row in the transaction to identify them) prior to issuing
a commit. If only one program performs inserts into Table2 then that
may be OK, but if you want to guarentee the relationship you need to
force the use of logic designed to maintain the relationship.

What happens when the value of the rows in table2 identified in table1
needs to change?
What about deletes from table2?

HTH -- Mark D Powell --

On Feb 16, 9:17*am, jharbo... (AT) googlemail (DOT) com wrote:
I can think of a way to accomplish this but I make no guarentees that
this method will be acceptable for your needs.

First as I understand the problem a transaction to insert rows into
table2 must insert exactly the number of rows as specified by table1
based on the value of the ID column.

This will only work if the table owner username is not used by the
application, that is, the application username must not have insert
on table2. Rename table2 and replace it with a view for selects.
Place an instead of trigger on the view that does not allow insert.
Code a package to update the renamed table. The package will check
the ID and store rows submitted for insert into a pl/sql table (array)
then when the proper number of rows have been submitted the
application indicates it has submitted all the data to be inserted.
The package checks the count and then performs the insert into the
renamed table (not the view) or issues an error.

Otherwise your application logic would have to do a count(*) of the
inserted rows (would require adding a transaction Id or the same date/
time to each row in the transaction to identify them) prior to issuing
a commit. If only one program performs inserts into Table2 then that
may be OK, but if you want to guarentee the relationship you need to
force the use of logic designed to maintain the relationship.

What happens when the value of the rows in table2 identified in table1
needs to change?
What about deletes from table2?

HTH -- Mark D Powell --

On 16.02.2008 22:02, Mark D Powell wrote:
How about this solution: add a column actual_count default 0 to table1.
Create a deferred constraint that ensures actual_count is either 0 or
equals to the other column value (the defining count).

Create a trigger on table2 which will increment and decrement
actual_count accordingly on insert / update / delete. Ensure that
application users cannot change the defining count (as you suggested).

Kind regards

robert

On 16.02.2008 22:02, Mark D Powell wrote:
How about this solution: add a column actual_count default 0 to table1.
Create a deferred constraint that ensures actual_count is either 0 or
equals to the other column value (the defining count).

Create a trigger on table2 which will increment and decrement
actual_count accordingly on insert / update / delete. Ensure that
application users cannot change the defining count (as you suggested).

Kind regards

robert

On 16.02.2008 22:02, Mark D Powell wrote:
How about this solution: add a column actual_count default 0 to table1.
Create a deferred constraint that ensures actual_count is either 0 or
equals to the other column value (the defining count).

Create a trigger on table2 which will increment and decrement
actual_count accordingly on insert / update / delete. Ensure that
application users cannot change the defining count (as you suggested).

Kind regards

robert

On 16.02.2008 22:02, Mark D Powell wrote:
How about this solution: add a column actual_count default 0 to table1.
Create a deferred constraint that ensures actual_count is either 0 or
equals to the other column value (the defining count).

Create a trigger on table2 which will increment and decrement
actual_count accordingly on insert / update / delete. Ensure that
application users cannot change the defining count (as you suggested).

Kind regards

robert

On Feb 16, 2:17*pm, jharbo... (AT) googlemail (DOT) com wrote:

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 (DOT) 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