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 |
|
GoodFella3993
Starting Member
8 Posts |
 Posted - 2009-06-01 : 12:00:39
|
| I have 3 fields (F1, F2, and F3). How can I validate to make sure only one field at row level is set to 'Y' and the remaining 2 fields are NULL?Thanks,Marc |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-06-01 : 12:31:43
|
| [code]CREATE TABLE YourTable( F1 char(1) NULL ,F2 char(1) NULL ,F3 char(1) NULL ,CONSTRAINT CK_YourTable_F123 CHECK ( (F1 = 'Y' AND F2 IS NULL AND F3 IS NULL) OR (F1 IS NULL AND F2 = 'Y' AND F3 IS NULL) OR (F1 IS NULL AND F2 IS NULL AND F3 = 'Y') ))[/code] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-01 : 13:55:10
|
, CONSTRAINT CK_YourTableF123 CHECK (COALESCE(F1, '') + COALESCE(F2, '') + COALESCE(F3, '') = 'Y')
E 12°55'05.63"N 56°04'39.26" |
|
|
|
GoodFella3993
Starting Member
8 Posts |
Posted - 2009-06-01 : 13:56:13
|
| Thanks, But I need this logic in my Stored Proc. Not as a table contraint. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-01 : 14:00:30
|
SELECT *FROM ...WHERE COALESCE(F1, '') + COALESCE(F2, '') + COALESCE(F3, '') = 'Y'
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-01 : 14:01:21
|
quote:
Originally posted by Peso
, CONSTRAINT CK_YourTableF123 CHECK (COALESCE(F1, '') + COALESCE(F2, '') + COALESCE(F3, '') = 'Y')
E 12°55'05.63"N 56°04'39.26"
wont this accept '' values for both fields and 'Y' for other field? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-01 : 15:43:22
|
We don't have enough information to make that assumption.There may be individual checks on each column allowing only "Y", "N" and NULL.If space character is an option, I hope OP would have told us. It's the decent thing to do.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
Validation: 3 fields. only one set to "Y"