Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Sunday, July 06, 2008

A one-one requirement constraint

The issue of a one-one requirement, a new order inserted must be
accompanied by a detail row (insert), was raised in the thread:

microsoft.public.sqlserver.programming
Thursday, June 26, 2008 
'update joined tables'
http://tinyurl.com/69hh5c

While this is a non-trivial exercise in sql it's quite easy in the
relational system Dataphor. For example:

create session table SOrders
{
 order_nbr:Integer,
 A:Integer,
 key{order_nbr}
}; 
 
create session table SOrderDetails
 {
  order_nbr:Integer,
  sku:Integer,
  B:Integer,
  C:String,
  key{order_nbr,sku},
  reference Details_SOrders{ order_nbr } references SOrders { order_nbr }
 };

This constraint makes a detail row a requirement for a new order. The user
defined message will be displayed anytime the constraint is violated.

alter table SOrders
  {
 create transition constraint MustHaveOrderDetails
  on insert 
    exists(SOrderDetails where order_nbr=new.order_nbr)  
   tags
 {
   DAE.Message =
          "'A detail row with order#: ' + ToString(new.order_nbr) + ' is required'" 
        }    
  };   
 
Inserting just a new order will violate the constraint:
insert row{1 order_nbr, 14 A} into SOrders; 
Violation of constraint "MustHaveOrderDetails", "A detail row with order#: 1 is required."

Inserting data can easily be done via a view.

create session view VSOrderDetails
                SOrders join SOrderDetails;//A natural join on order_nbr.

The MustHaveOrderDetails constraint works for the insertion of a single
row or a table.

insert row{1 order_nbr, 1 sku, 14 A, 5 B, 'Joe' C} into VSOrderDetails;     

delete SOrders;

Insert into the tables thru the view.

insert table
            {
             row{1 order_nbr, 1 sku, 14 A, 5 B, 'Joe' C},
             row{1,2,9,23,'Steve'},
             row{2,3,34,2,'Larry'}
            } into VSOrderDetails;   

select SOrders;

order_nbr A  
--------- -- 
1         14 
2         34 

select SOrderDetails;            

order_nbr sku B  C     
--------- --- -- ----- 
1         1   5  Joe   
1         2   23 Steve 
2         3   2  Larry 

This rather simple example shows off the much higher level of abstraction
offered to developers with a relational system like Dataphor over sql. In
sql only one table may be updated with a view. In Dataphor there is no
concept of updating multiple tables. There is only the idea of updating a
view/expression which is a table. It's the system responsibility to resolve
a view to its base tables given the constraints in the schema. The user need
only realize a table is being used for data modification and it's irrelevant
how that table was constructed as well as the number of tables in its definition.

No comments:

About Me

My photo
Phoenix, Arizona, United States