Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Help with Oracle constrant on two tables problem

4 views
Skip to first unread message

jhar...@googlemail.com

unread,
Feb 16, 2008, 9:17:16 AM2/16/08
to

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

Mark D Powell

unread,
Feb 16, 2008, 4:02:07 PM2/16/08
to

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

Robert Klemme

unread,
Feb 17, 2008, 1:41:14 PM2/17/08
to

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

Michael O'Shea

unread,
Feb 18, 2008, 3:42:18 AM2/18/08
to


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 Michae...@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

Robert Klemme

unread,
Feb 18, 2008, 2:10:09 PM2/18/08
to

> 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

jhar...@googlemail.com

unread,
Feb 18, 2008, 2:25:12 PM2/18/08
to

Hello Robert and Michaeland thank you for your SQL.

I think your SQL is to complicated and, taking your SQL and changing
it this is what I need

ALTER TABLE tblT2
ADD CONSTRAINT DI_CorrectMethodSetCount
CHECK exists (SELECT tblt2.id,tblt1.id1
FROM tblT1, tblT2
WHERE tblT1.id1 = tblT2.id1
AND tblT1.id1 = tblt2.id1


GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id

HAVING COUNT(*) = tblT1.checkCount-1);

My Oracle SQL isn't right and when I type this in I get an error.


Could you correct it please

Thank you

Jon

fitzj...@cox.net

unread,
Feb 18, 2008, 2:43:42 PM2/18/08
to

You could post the SQL but not the error it generates?


David Fitzjarrell

jhar...@googlemail.com

unread,
Feb 18, 2008, 4:37:38 PM2/18/08
to
Hello David.

When I type

ALTER TABLE tblT2
ADD CONSTRAINT DI_CorrectMethodSetCount
CHECK exists (SELECT tblt2.id,tblt1.id1
FROM tblT1, tblT2
WHERE tblT1.id1 = tblT2.id1
AND tblT1.id1 = tblt2.id1
GROUP BY tblT1.checkCount, tblt1.id1, tblt2.id
HAVING COUNT(*) = tblT1.checkCount-1);

my error is

ORA-00906: missing left parenthesis


Thank you
Jon

Robert Klemme

unread,
Feb 19, 2008, 5:05:01 AM2/19/08
to

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm#sthref3116

robert

Michael O'Shea

unread,
Feb 19, 2008, 8:23:03 AM2/19/08
to
>         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.

0 new messages