sql server - Adding a lowercase constraint to a column in sql -

i've searched web found how update columns lowercase. there way put constraint in column accepts lowercase , gets error when try insert in uppercase.

the table following:

create table student (   id int,   name varchar(50),   email varchar(50) ); 

you can use check constraint binary_checksum function this:

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

you can use multiple conditions in check, check email-address contains @ character , on, although processing demand increase:

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

it better check input in client side application insert it, or use trigger or stored procedure handle inserts , force data lower case.

to quote sql server manual:

checksum , binary_checksum return different values string data types, locale can cause strings different representation compare equal. string data types char, varchar, nchar, nvarchar, or sql_variant (if base type of sql_variant string data type). example, binary_checksum values strings "mccavity" , "mccavity" different. in contrast, in case-insensitive server, checksum returns same checksum values strings. checksum values should not compared binary_checksum values.