#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0

    Data type for simple 'Yes/No' choice?


    Hi,
    New to SQL. Just recently installed MS SQL Management Studio 2014, and created a little test db.

    One of the columns, I would like to be a simple yes/no option.
    I set it as data type = bit.

    For the life of me I cannot find where 'field validation' is configured.

    Any advice, please?

    Thank you.
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,967
    Rep Power
    9647
    BIT fields are merely 0 or 1. Not sure what you mean by "field validation" but there's nothing really to validate.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    Column is "Has pets", so data would be either Y or N
    Can't see how to achieve this, yet I'm sure it's probably very simple.

    Thank you.
  6. #4
  7. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,204
    Rep Power
    2012
    Do you want to store the result as 'Y' or 'N' in the database? Or will it be enough to have it as '1' (true/yes) or '0' (false/no)

    If 1/0 is ok, then use the BIT field. If you want to use Y/N, then you will have to look into CHAR field or similar.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    I'd like to store Y or N, and allow only those characters to be accepted.

    Thanks.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by Richard238
    I'd like to store Y or N, and allow only those characters to be accepted.
    use CHAR(1) and declare a CHECK CONSTRAINT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    I tried, amongst dozens of other permutations: CHECK column4 = 'Y' OR column4 = 'N'

    Why's that not happy?

    Thank you.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    nor this either: CHECK (Outsides LIKE '[YN]')
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    i'm sorry, i do not understand "not happy"

    please show entire CREATE TABLE statement and the accompanying error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    Right-click table designer / check constraint

    Name = CK_tableName_yn

    Enter expression... " CHECK column4 = 'Y' OR column4 = 'N' " or " CHECK (column4 LIKE '[YN]') "

    Pop-up error "error validating constraint"

    Thank you.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    sorry, i cannot help you, maybe someone else

    never used table designer

    i'll guess, though, that perhaps the word CHECK is not needed?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    Learned that you can't create a Check Constraint, retrospectively. Can only be applied to an empty table.

    Simple, when you know how!
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by Richard238
    Learned that you can't create a Check Constraint, retrospectively.
    pretty sure you can

    but if you're good to go, then okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Location
    London, UK
    Posts
    8
    Rep Power
    0
    Maybe you can, but that'll do for me.
    For now.....

IMN logo majestic logo threadwatch logo seochat tools logo