T-Sql: How to Create a Unique Key That Is Case Sensitive

T-SQL: How do I create a unique key that is case sensitive?

This will change the column to be case sensitive. I don't think there's any change to your constraint...

ALTER TABLE mytable 
ALTER COLUMN mycolumn VARCHAR(10) 
COLLATE SQL_Latin1_General_CP1_CS_AS

Any selects or joins on this column will become case sensitive as a result of this operation.

SQL Server 2008 Unique Column that is Case Sensitive

The uniqueness can be enforced with a unique constraint.

Whether or not the unique index is case-sensitive is defined by the server's (or the table's) collation.

You can get the current collation of your database with this query:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

and you should get something like:

SQLCollation
————————————
SQL_Latin1_General_CP1_CI_AS

Here, the "CI_AS" at the end of the collation means: CI = Case Insensitive, AS = Accent sensitive.

This can be changed to whatever you need it to be. If your database and/or table does have a case-sensitive collation, I would expect that the uniqueness of your index will be case-sensitive as well, e.g. your abcdef and ABCDEF should be both acceptable as unique strings.

Marc

UPDATE:

I just tried this (SQL Server 2008 Developer Edition x64) - works for me (my database is generally using the "Latin1_General_CI_AS collation, but I can define a different one per table / per VARCHAR column even):

CREATE TABLE TestUnique
    (string VARCHAR(50) COLLATE SQL_Latin1_General_Cp1_CS_AS)

CREATE UNIQUE INDEX UIX_Test ON dbo.TestUnique(string)

INSERT INTO dbo.TestUnique(string) VALUES ('abc')
INSERT INTO dbo.TestUnique(string) VALUES ('ABC')

SELECT * FROM dbo.TestUnique

and I get back:

string
ABC
abc

and no error about the unique index being violated.

Make Varchar PK case-sensitive?

One option is to change the collation of the database to one that is case ensitive, like SQL_Latin1_General_CP1_CS_AS.

You can do this in SQL Server Management Studio via the Object Explorer by right-clicking on the database and going to "Properties > Options" then the "Collation" drop-down. Any one that has "CS" in the name is case-sensitive.

Of course, as was pointed out in the comments, this is probably overkill. You can also override the collation for just one column, as described here:

https://msdn.microsoft.com/en-us/library/ms190920.aspx

SQL: How to create table with case sensitive for unique key for utf8mb4_general_ci?

Thank @tkausl and @topsail.

The utf8mb4_general_ci contain _ci that for case insensitive.

From https://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql,
I should use utf8mb4_bin for the unique column that I want case sensitive and I can use ORDER BY column COLLATE utf8_general_ci.

SQL unique varchar case sensitivity question

Looks like mysql is case insensitive by default:

You probably need to create the column with a case sensitive collation (e.g. utf8_bin):

CREATE TABLE WORDS (
    ID BIGINT AUTO_INCREMENT, 
    WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE, 
    PRIMARY KEY(ID)
);

How do you impose a case insensitive unique constraint on a Firebird field value?

You can add a unique index on lower of email column on the table like so:

create unique index email_unq_idx on agent computed by (lower(email));

Making unique key case insensitive

The simpliest is to add BINARY on the DDL statement,

`NAME` varchar(128) BINARY NOT NULL

SQLFiddle Demo