Re: BIG BUG in Deleting detali records
- From: Arthur Hoornweg <antispam.hoornweg@xxxxxxxxxxxxx>
- Date: Fri, 15 Jun 2007 14:24:36 +0200
Cavalera Guido wrote:
... but there is a workaround ? to use delete details on batchOptimistic ?
You must change the declaration of the client table
in the database in order to enforce referential integrity.
Referential integrity is a "must have" in a database and
should never be left to the application IMHO.
It is easy to implement. Just add a "constraint" to the client
table so that the ID that references the master table becomes
a foreign key.
Specifying "on delete cascade" for that foreign key will cause
all detail records of a customer to be deleted automatically
when the customer himself is deleted from the master table.
E X A M P L E
-------------------
Here is a little example. I haven't double-checked the syntax
but it should give you a pretty good idea what's it about.
In "real life" one would of course also have to add some indexes
for speed and select appropriate data types for the ID's (integers
have a way of wrapping around after a few billion entries).
In this example, the "constraints" enforce that whenever you
delete an order, the items belonging to that order are deleted
also, in a master-detail way. But you can still delete individual items
belonging to an order.
Also, when you delete a customer, all orders for that customer
are deleted from the database (which, in turn, triggers the
deletion of the appropriate items). This snowball effect is called
"cascading".
Deleting a "product" is made impossible if that product has
ever been ordered by anyone. This way the tables are
always in a valid state - guaranteed.
create table [dbo].customers(
customerID int identity (1,1) not null primary key,
customername varchar(50) not null);
create table [dbo].products(
productID int identity (1,1) not null primary key,
productname varchar(50) not null,
productprice currency);
create table [dbo].orders(
orderNR int identity (1,1) not null primary key,
customer int,
orderdate datetime not null default current_timestamp,
paymentdate datetime null,
amountpaid currency,
ordercancelled bit not null default 0,
constraint c1 foreign key(customer) references [dbo].customers(customerID)
on update no action on delete cascade )
create table [dbo].orderitems
ItemID identity (1,1) not null primary key,
ordernr int,
product int,
quantity int,
constraint c2 foreign key (ordernr) references [dbo].orders (orderNR)
on update no action on delete cascade,
constraint c3 foreign key (product) references [dbo].products (productID)
on update no action on delete no action)
--
Arthur Hoornweg
(In order to reply per e-mail, please just remove the ".net"
from my e-mail address. Leave the rest of the address intact
including the "antispam" part. I had to take this measure to
counteract unsollicited mail.)
.
- Prev by Date: Re: Speed issue...
- Next by Date: Re: MSDE Performance and Reliability
- Previous by thread: Programmatically get OLDDB Version (and other properties)
- Next by thread: cannot write into CSV using ADO
- Index(es):
Relevant Pages
|
