T SQL CHECK Constraint to allow uppercase set of values

Question

I have to create a table in SQL Server using a check constraint to allow only Uppercase set of values

My code as this:

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 the Error and help me

Answer 1

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

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 stands for Case Sensitive. The 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)
);