CODEWITHSUNDEEP
sqlsql-servert-sqlcheck-constraint
user2473015
user2473015

Reputation: 1442

T SQL CHECK Constraint to allow uppercase set of values

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

Upvotes: 4

Views: 4474

Answers (2)

user2473015
user2473015

Reputation: 1442

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)
);

Upvotes: 0

Andomar
Andomar

Reputation: 238048

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.

Upvotes: 7

Related Questions