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 |
|
sha_agrawal
Starting Member
24 Posts |
 Posted - 2010-01-21 : 08:49:21
|
| I am using sql server 2000. I am having 2 fields in a table i.e. Qty and Amount. I want to implement Constraint on the table so that any record containg qty but not amount, can not be saved. I tried Qty=0 and Amount<>0 but it does not work. Pls. help me |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-21 : 11:26:20
|
quote:
can not be saved
Do you mean cannot be inserted?Did you try using a CHECK constraint..CONSTRAINT chk_table CHECK (Qty > 0 AND Amount > 0) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 11:43:58
|
Allow for Amount IS NULL perhaps?CONSTRAINT chk_table CHECK (Qty > 0 AND COALESCE(Amount, 0) > 0) |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-21 : 12:00:29
|
Or this maybe??constraint chk_temp check (case when qty > 0 and amount is null then 1 else 0 end = 0 ) |
|
|
|
sha_agrawal
Starting Member
24 Posts |
Posted - 2010-01-28 : 07:32:01
|
| Thanks a lot 'vijayisonly', it worked, but could you pls.explain what is the meaning of 'end=0' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 08:02:53
|
"case when qty > 0 and amount is null then 1 else 0 end"This gives you "1" (You have Qty but no Amount) otherwise if gives you "0"The outcome of the CASE statement is then tested for "= 0"So either "1 = 0" (You have Qty but no Amount) or else you get "0 = 0"Only "0 = 0" is true  so if "You have Qty but no amount" your check constraint will be "1 = 0" which is false |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-01-28 : 09:58:47
|
Wow...I dont think I could have done a better done at explaining this. Nice work Kristen.  |
|
|
|
sha_agrawal
Starting Member
24 Posts |
Posted - 2010-02-01 : 08:08:55
|
| Thanks to both of you. |
|
|
|
|
|
|
define constraint