Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jdet
Starting Member
3 Posts |
 Posted - 2012-04-10 : 12:47:02
|
Ok I'd to say I'm new around these parts and if this is not the right place for this question, I apologize.So this database is for our church and we just want to keep track of mainly our members, where they serve and what they donate. This is for a final project class and will hopefully be implimented in the future. Our church has a business rule that no person can become a member before age 16. So I was trying to write a check constraint to inforce this rule. Here is what I came up with:
alter table dbo.memberadd constraint chk_member_age_16 CHECK (visitor_indicator in ('N') and datediff(year,[date_of_birth],getdate())> 16 );
Now after some troubleshooting I found out what this does is make sure that all the visitor indicators are turn to 'N' AND that they are over 16. This is obviously not what I want it to do because with that in place we could have no visitors. So my question is how do I write a check constraint that will make sure that both statements are true together and not treated as aeperate statements. Here are some links to my ERD[url]http://dl.dropbox.com/u/21980186/ERD.png[/url]Inserts[url]http://dl.dropbox.com/u/21980186/inserts.txt[/url]and DDL[url]http://dl.dropbox.com/u/21980186/289%20DDL.ddl[/url]The ddl is generated from erwin so I know it's a little rough but it works. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-04-10 : 12:55:23
|
I'm not sure I understand the logic, but maby you could make use of a case expression? I assume the lofic is mroe complex, otherwise you wouldn't need the case expression.add constraint chk_member_age_16 CHECK ( CASE WHEN visitor_indicator = 'N' and datediff(year,[date_of_birth],getdate()) < 16 THEN 0 ELSE 1 END = 1) |
 |
|
|
jdet
Starting Member
3 Posts |
Posted - 2012-04-10 : 13:29:05
|
| It looks like you understood my logic perfectly because this worked like a charm! I never used a case statement in a check constraint before so just to make sure I'm understanding it correctly, the only way the case statement will end is if the case = 1 and when the condition is true it sets the case value to 0 so the check constraint will not pass. Is this right? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-04-10 : 13:53:20
|
Yes the CASE expression will evaluate to 1 or 0 depending. Comparing the result of the case expression to 1 then results in a boolean that determines the success or failure of the check constraint.If that worked for you, then I think you can simplify it slightly using the NOT operator and removing the case:add constraint chk_member_age_16 CHECK( NOT(visitor_indicator = 'N' and datediff(year,[date_of_birth],getdate()) < 16)) I didn't test it, but I think that'd work. |
|
|
|
jdet
Starting Member
3 Posts |
Posted - 2012-04-10 : 15:24:34
|
| Wow, its really cool how such a simple switch in logic can change something from not working to working perfectly.I understand why and everything but I just love how a little change can make a world of difference. Thanks so much for your help. I look forward to lurking around here for a while and learning. |
|
|
|
|
|
|
|
|
Check Constraint with two conditions