enforce referential integrity on only rows meeting criteria?
Posted on 2011-04-27
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.