Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Answered: Constraint Check for Case Sensitivity Regardless Collation?

    Can I have a check constraint on a "state_code" column that verifies the users are entering valid state codes in UPPER() despite my default server setting being CI?
    Code:
    CONSTRAINT ck_state_code CHECK (UPPER(state_code IN (FL, NY, CA, TX, WA)));

  2. Best Answer
    Posted by Pat Phelan

    "This is a bad idea, but here's how to do it:
    Code:
    IF Object_Id ('upper_constraint', 'U') IS NOT NULL
       DROP TABLE upper_constraint
    GO
    
    CREATE TABLE upper_constraint (
       bad_idea CHAR(2) NOT NULL
          CONSTRAINT XCK_upper_constraint_bad_idea
    	  CHECK (bad_idea COLLATE Latin1_General_BIN IN ('FL', 'NY', 'CA', 'TX', 'WA'))
       )
    
    -- This works
    
    INSERT INTO upper_constraint (bad_idea)
       VALUES ('FL'), ('NY'), ('CA'), ('TX'), ('WA')
    
    -- None of these work
    
    INSERT INTO upper_constraint (bad_idea) VALUES ('fl')
    INSERT INTO upper_constraint (bad_idea) VALUES ('ny')
    INSERT INTO upper_constraint (bad_idea) VALUES ('ca')
    INSERT INTO upper_constraint (bad_idea) VALUES ('tx')
    INSERT INTO upper_constraint (bad_idea) VALUES ('wa')
    
    -- This still works
    
    INSERT INTO upper_constraint (bad_idea)
       VALUES ('FL'), ('NY'), ('CA'), ('TX'), ('WA')
    
    -- and this proves that it worked
    
    SELECT Count(*), bad_idea
       FROM upper_constraint
       GROUP BY bad_idea
    The reason that this is a bad idea is because the upper or lower conversion is simple to do on ETL, and makes no sense or difference to the end user. I won't force people to follow rules unless there is a compelling reason and forcing case is purely arbitrary... I see no business or technical reason for making people type upper, lower, or anything else when it is so easy for SQL to make the characters be any appropriate case.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a bad idea, but here's how to do it:
    Code:
    IF Object_Id ('upper_constraint', 'U') IS NOT NULL
       DROP TABLE upper_constraint
    GO
    
    CREATE TABLE upper_constraint (
       bad_idea CHAR(2) NOT NULL
          CONSTRAINT XCK_upper_constraint_bad_idea
    	  CHECK (bad_idea COLLATE Latin1_General_BIN IN ('FL', 'NY', 'CA', 'TX', 'WA'))
       )
    
    -- This works
    
    INSERT INTO upper_constraint (bad_idea)
       VALUES ('FL'), ('NY'), ('CA'), ('TX'), ('WA')
    
    -- None of these work
    
    INSERT INTO upper_constraint (bad_idea) VALUES ('fl')
    INSERT INTO upper_constraint (bad_idea) VALUES ('ny')
    INSERT INTO upper_constraint (bad_idea) VALUES ('ca')
    INSERT INTO upper_constraint (bad_idea) VALUES ('tx')
    INSERT INTO upper_constraint (bad_idea) VALUES ('wa')
    
    -- This still works
    
    INSERT INTO upper_constraint (bad_idea)
       VALUES ('FL'), ('NY'), ('CA'), ('TX'), ('WA')
    
    -- and this proves that it worked
    
    SELECT Count(*), bad_idea
       FROM upper_constraint
       GROUP BY bad_idea
    The reason that this is a bad idea is because the upper or lower conversion is simple to do on ETL, and makes no sense or difference to the end user. I won't force people to follow rules unless there is a compelling reason and forcing case is purely arbitrary... I see no business or technical reason for making people type upper, lower, or anything else when it is so easy for SQL to make the characters be any appropriate case.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •