Re: Help with Oracle constrant on two tables problem

From: Robert Klemme <>
Date: Sun, 17 Feb 2008 19:41:14 +0100
Message-ID: <>

On 16.02.2008 22:02, Mark D Powell wrote:
> On Feb 16, 9:17 am, 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

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

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 Received on Sun Feb 17 2008 - 12:41:14 CST

Original text of this message