?
Solved

enforce referential integrity on only rows meeting criteria?

Posted on 2011-04-27
9
Medium Priority
?
312 Views
Last Modified: 2012-08-13
I've got a SQL 2005 database with a checks in a table. For the past 3 years, every check has had a valid OrderNo in it in its OrderNo column. Prior to 3 years ago, checks were created and many of them have invalid OrderNo's on them (sometimes people typed an OrderNo as "123 and 456".

I'd like to enforce cascading updates but can't figure out how to do that. When I add a relationship to a diagram, I get an error since not all the checks have valid OrderNo's. I can probably figure out how to create dummy orders for all my checks with invalid values, but I'm wondering if there's another way to go about this.

Is there a way to enable cascading updates of the OrderNo column based on a condition? Say, only where year(CheckDate) > 2007 or something like that.
0
Comment
Question by:GordonPrince
  • 5
  • 4
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480119
>Is there a way to enable cascading updates of the OrderNo column based on a condition?
not that I know ... only with trigger
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35480377
so how would you write the trigger?
put the trigger on table1
as

after update of table1
update table2 set foreignkey = inserted.key
where table2.foreignkey = deleted.key

or something like that? Will that work?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35480409
well, OrderNo being a primary key somewhere, it should not change?
can you please clarify the relevant schema design, and from where to where the update should be cascaded?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:GordonPrince
ID: 35482581
OrderNo is the primary key, and it is what's changing. I know that's not the best design (it's a varchar(35), plus the primary key is OrderNo + another column), but that's the way it is.

The OrderNo isn't generated by anything I can control, it comes from somewhere else. Occasionally it gets entered wrong at the beginning, data in related tables is added, then it needs to be changed. I've got cascading updates working with all the other tables, except this one RefundCheck table.

The schema is
for each Order 0/1/many Refunds
for each Refund 0/1/many RefundChecks
There's referential integrity between Order and Refunds (and the cascading updating is working there), but not between Refund and RefundChecks. Because several years' data exists for RefundChecks that doesn't have valid Order information (some were combined, etc.).

I've got a trigger on the Refund table that prevents a row from being changed if it would orphan a row in the RefundChecks table. It's firing now if I changed the OrderNo in the Order table.

So maybe the trigger would be on the Refund table as:
after update of Refund
update RefundCheck set OrderNo = inserted.OrderNo
where RefundCheck.OrderNo= deleted.OrderNo



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35482614
yes, that would work, but only if there is one single record updated at a time
update RefundCheck 
   set OrderNo = ( select OrderNo from inserted )
 WHERE OrderNo = ( select OrderNo from deleted )
   and ( select OrderNo from deleted ) <> ( select OrderNo from inserted )

Open in new window

0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35495057
Looks like we're close. I've added the trigger to the Refund table, so when OrderNo updates, it updates the OrderNo column in RefundCheck. When I check my original statement to confirm that exactly 1 row updated (update Order set OrderNo = NewOrderNo where OrderNo = OldOrderNo) I get the number of records processed as the number of RefundChecks that were updated -- which could be 0, 1, or many.

Is there a way to leave this logic in the application and have the SQL statement return the number of rows updated in the Order table instead of the number of rows updated in the last table updated by the last trigger that fires? Or am I being paranoid testing to make sure that exactly one row was updated in my code?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35496924
you could check in the trigger if 1 max row was updated ... and if not, rollback + raiserror ...
0
 
LVL 4

Author Comment

by:GordonPrince
ID: 35497320
I put the trigger to update the RefundCheck rows in the Refund table. Maybe it should be in the Orders table. Otherwise I won't be able to tell that one row was updated in the Orders table. Or am I thinking about this right?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35497498
you see it correctly
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question