| Author |
Topic |
|
hippu
Starting Member
10 Posts |
 Posted - 2004-06-09 : 14:01:16
|
| Hello,I am facing a problem.I have a table T with Columns C1, C2. And I am introducing two constrins in the table as following.C1 can have two valid values, V1 or V2 - Hence, I put that as constraint CK_C1.Now, valid values for C2 are dependent on value of C1. C2 shuld be V3 when C1 is V1; C2 shuld be V4 when C1 is V2. Accordingly I introduced the second constraint CK_C2.Now, I want ALWAYS CK_C1 to be evaluated BEFORE CK_C2. Please guide me HOW TO ENSURE THAT ?Thanks and Regards,Hippu |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 14:08:31
|
| According to BOL, you should create one check constraint that references multiple columns:It is possible to apply multiple CHECK constraints to a single column. These are evaluated in the order in which created. It is also possible to apply a single CHECK constraint to multiple columns by creating it at the table level. For example, a multiple-column CHECK constraint can be used to confirm that any row with a country column value of USA also has a two-character value in the state column. This allows multiple conditions to be checked in one place.Tara |
 |
|
|
hippu
Starting Member
10 Posts |
Posted - 2004-06-09 : 14:14:35
|
| Thanks Tara,Well, I will note the guide line that "one should create one check constraint that references multiple columns". (By the way what is BOL?)But, my question still remains unanswered. How I will ensure one constraint to be evaluated before/after other. We can assume both the constriant are table-level constraints.Regards,Hippu |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 14:16:30
|
| BOL is SQL Server Books Online.Your question has been answered. See the comment in BOL about a multiple-column CHECK constraint. They give an example of country column of USA that forces a two-character value in the state column. There is no other way to ensure that CK_C1 gets evaluated before CK_C2.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-09 : 14:23:19
|
quote:
Originally posted by tduggan
These are evaluated in the order in which created.
Yes I guess it's been answered...But I guess you could mimic constraints inside a trigger...Brett8-) |
|
|
|
hippu
Starting Member
10 Posts |
Posted - 2004-06-09 : 14:26:50
|
| Thanks for prompt replies.But, that example is illustrated to explain a scenario of use/application a multiple-column CHECK constraint.And my question was - If I have two constraints (assume both are multiple-column type) - then how to ENSURE that one is evaluated after the other. Please suggest. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 14:39:28
|
| Well the evaluation in the order in which created refers to check constraints on the same column and not on multiple columns. I guess it's possible that the same applies though. You will probably have to enforce this through a trigger or through your application if evaluation in the order in which they are created does not apply to multiple columns.I still think that you can use the multiple column check constraint though. You'll just have to modify what you have already done.Tara |
|
|
|
hippu
Starting Member
10 Posts |
Posted - 2004-06-09 : 14:49:45
|
| Thanks for the suggestion.I guess I will use trigger to enforce the sequence. "Creation order" is not completely dependable as it may so happen that on a later date CK_C2 gets deleted and recreated. Then also the sequence should be maintained. Thanks a lot.Regards,Hippu |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-09 : 15:05:41
|
| I still can't see why...Any and all constraints need to be true or the DML will failWhy does it matter?Brett8-) |
|
|
|
hippu
Starting Member
10 Posts |
Posted - 2004-06-09 : 15:10:52
|
| Hello Brett,In my case, I do not want to re-check certain things in CK_C2 which are already checked in CK_C1 as I feel it is a overhead. Therefore I want to sequence them.Regards,Hippu |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-09 : 15:51:51
|
| Really?Can you post the actual DDL for the constraints that you're talking about?And put them in the order you want,Or are they not written yet.I don't know how much performance wise you'll be saving though.I would quess the trigger would cost more....Brett8-) |
|
|
|
|
Ordering table level Constraints