Re: L:ist - Can do/do better in MS SQL than Oracle

In article <3cada501.20854171_at_news.nl.uu.net>, benbrugman_at_onbekend.nl says...
>
>On Thu, 4 Apr 2002 21:37:18 +0100, "Niall Litchfield"
><niall.litchfield_at_dial.pipex.com> wrote:
>
>>the set transaction isolation level serializable example is dead on - it
>>don't work in oracle. Of course the actual design for the simplified eg is
>>terrible ; withdrawal of funds from a net balance in two different tables
>>and update a third withdrawal table wtf? however I tried the example as
>>stated and broke the business rule (coding to follow - its at work not
>>home). maybe if there were a real life eg where this mattered i'd care
>>more....
>In the financial world it is fairly common to add information about
>financial transactions as rows and not updating existing rows. This
>can break business rules. (No predicate lock exists in Oracle).
>
>A practical example we came across.
>A master record which can have details. Deleting of the master is
>done by using a status field, (setting this status field to not_valid
>for example). This can only be done if there are no details for this
>master.
>Another process can write a detail at te same time a masterrecord
>is made not_valid.
>
>Removing a masterrecord
>Within the transaction a check is made for existing details. If there
>is none the master can be made not_valid.
>
>Adding a detailrecord
>Within the transaction a check is made for existence of a valid
>master record while adding a detail.
>
>This can lead to breakage of the business rule.
>
>One can make a constraint on the master detail relationship but
>if this does not include the status field it does not work. If this
>includes the statusfield then there is a statusfield in the detail
>always containing the same value. (Rather silly to me).

use null, no data, no worry. does sound like a data model issue to me more then anything.

When you consider the amount of code you will need to write and write and write (cause you never do this just once, you will interact with this table over and over) the "silly" argument seems to go away. The amount of coding and maintenance and testing you save seems to more then pay for itself.

>
>So to solve this problem we will refert to an application solution.
>
>ben brugman

or declaritive integrity:

create table master ( pk number primary key ,

                      status varchar2(10),
                      constraint pk_status_unique unique(pk,status)
                    )

create table detail
( pk int primary key,
  fk1 number,
  fk2 varchar2(10) default null check ( fk2 is null ),   foreign key(fk1,fk2) references master(pk,status) )
/

say status is NULL when record is "valid" (status can have any value, I'm just using NULL here).

since the status field in detail can only take the "valid" value -- a master record cannot take on the "not_valid" value while a child exists. So, #1

>A master record which can have details. Deleting of the master is
>done by using a status field, (setting this status field to not_valid
>for example). This can only be done if there are no details for this
>master.

is satisfied.

As for:

>Removing a masterrecord
>Within the transaction a check is made for existing details. If there
>is none the master can be made not_valid.

again, this is done for you. You cannot delete a master with children. The update to the status can only happen if a child does not exist. You need check nothing.

And likewise for:

>Adding a detailrecord
>Within the transaction a check is made for existence of a valid
>master record while adding a detail.
>

It just seems to me that in your example, the primary key of the master might be "wrong" -- the status field should be part of the primary key. If it isn't -- no worries, just unique it and the detail points to it.

This is something that should definitely be done declaritively.

>
>
>
>
>>
>>
>>--
>>Niall Litchfield
>>Oracle DBA
>>Audit Commission UK
>>
>>
>
>Ben Brugman

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp