T SQL CHECK Constraint for upper case resolution

Question

I need to create a table on SQL Server using a validation constraint to allow only the top set of values

My code is:

CREATE TABLE Client(
    clientID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    StreetAddress VARCHAR(50),
    Suburb VARCHAR(25),
    State VARCHAR(4) CHECK (state in ('QLD', 'NSW', 'VIC', 'TAS', 'SA', 'WA', 'NT', 'ACT')),
    PhoneNumber VARCHAR(10)
);

Please check Error and help me

Answer 1

By default, SQL Server is case-insensitive. You can define a column as case sensitive by using case-sensitive sorting:

declare @t table (state varchar(4) check (state in ('ok', 'computer')))
insert @t values ('OK') -- No error, case insensitive by default
go
declare @t table (state varchar(4) collate SQL_Latin1_General_CP1_CS_AS 
    check (state in ('ok', 'computer')))
insert @t values ('OK') -- The INSERT statement conflicted with the CHECK constraint

Note the CS in SQL_Latin1_General_CP1_ CS _AS, which means Case Sensitive. AS stands for Accent Sensitive.

Answer 2

CREATE TABLE Client(
    clientID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    StreetAddress VARCHAR(50),
    Suburb VARCHAR(25),
    State VARCHAR(4) CHECK (state in ('QLD', 'NSW', 'VIC', 'TAS', 'SA', 'WA', 'NT', 'ACT')) COLLATE SQL_Latin1_General_CP1_CS_AS ,
    PhoneNumber VARCHAR(10)
);