Adding a lowercase constraint to a column in sql

Question

I've searched the web but found only how to update columns to lowercase. Is there a way to put a constraint in a column so that it accepts only lowercase and gets an error when we try to insert something in uppercase.

The table is the following:

create table Student
(
  ID int,
  Name varchar(50),
  Email varchar(50)
);

Answer 1

You can use a check constraint with the binary_checksum function for this:

alter table student add check (binary_checksum(email) = binary_checksum(lower(email)));

You can also use multiple conditions in the check, so you could also check that the email-address contains a @ character and so on, although processing demand would increase:

check (binary_checksum(email) = binary_checksum(lower(email)) and charindex('@', email) > 0);

It would probably be better to check the input in the client side application where you insert it, or use a trigger or stored procedure to handle inserts and force data to be lower case.

To quote the SQL Server manual:

CHECKSUM and BINARY_CHECKSUM return different values for the string data types, where locale can cause strings with different representation to compare equal. The string data types are char, varchar, nchar, nvarchar, or sql_variant (if the base type of sql_variant is a string data type). For example, the BINARY_CHECKSUM values for the strings "McCavity" and "Mccavity" are different. In contrast, in a case-insensitive server, CHECKSUM returns the same checksum values for those strings. CHECKSUM values should not be compared with BINARY_CHECKSUM values.

Answer 2

Create a check constraint that compares the actual value with the lower value:

create table Student
(
  ID int,
  Name varchar(50),
  Email varchar(50),
  constraint check_lowercase_email check (lower(email) = email)
);

The above however will not work if your default collation is case insensitive in that case you need to do something like this:

create table Student
(
  ID int,
  Name varchar(50),
  Email varchar(50),
  constraint check_lowercase_email check (lower(email) = email COLLATE Latin1_General_CS_AI )
);

Which collation you use depends on what the default collation of your database is, but usually there is a CS version available which stands for "case insensitive". Latin1_General_BIN would probably work just as well for emails.