Re: Help with Oracle constrant on two tables problem
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sun, 17 Feb 2008 19:41:14 +0100
Message-ID: <61rdecF1u3r0oU1@mid.individual.net>
>
> 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?
Date: Sun, 17 Feb 2008 19:41:14 +0100
Message-ID: <61rdecF1u3r0oU1@mid.individual.net>
On 16.02.2008 22:02, Mark D Powell wrote:
> On Feb 16, 9:17 am, 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
>
> 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