Re: Help with Oracle constrant on two tables problem

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?

HTH -- Mark D Powell -- Received on Sat Feb 16 2008 - 15:02:07 CST