Make Varchar PK case-sensitive?

Question

I have to store Ids of type Varchar in my SQL Server database table. The Ids are unique and they will be used for update/delete operations.

So I decided to make them PK. The problem is, that only one of the PKs "A" and "a" is allowed, while both may be in the dataset I have to import.

INSERT INTO test(test) VALUES('A')
INSERT INTO test(test) VALUES('a')
Violation of the PRIMARY KEY constraint 'PK_test'. Cannot insert duplicate key in object 'dbo.test'.

Can I make the PK case-sensitive - so that after "A" is inserted, "a" may be inserted but "A" may be not?

possible duplicate of [T-SQL: How do I create a unique key that is case sensitive?](http://stackoverflow.com/questions/485359/t-sql-how-do-i-create-a-unique-key-that-is-case-sensitive) — GarethD, Jan 29 '15 at 12:51

rory.ap · Accepted Answer · 2015-01-29T12:54:56.463

2

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

edited Jan 29 '15 at 12:54

answered Jan 29 '15 at 12:46

rory.ap

34,009
10
83
174

3

*cough* you mean to a case *sensitive* collation - the user wants A and a to be two different, valid PKs – Obsidian Phoenix Jan 29 '15 at 12:48
You mean "case sensitive". – David Brabant Jan 29 '15 at 12:48
@ObsidianPhoenix -- No, I don't. "Case Insensitive" means you can do exactly what he wants because the collation refers to *comparing* values, meaning that `A` and `a` will be treated the same. *cough*. – rory.ap Jan 29 '15 at 12:49
3

Changing the collation of the *whole database* is way overkill, as the collation on the PK column can be changed, as in https://msdn.microsoft.com/en-us/library/ms190920.aspx - if the whole database is case sensitive, it has knock-on effects, eg all variable names in stored procedures become case sensitive. – stuartd Jan 29 '15 at 12:50
1

Exactly. And thats NOT what the OP wants. – Obsidian Phoenix Jan 29 '15 at 12:50
@stuartd -- probably. It's one option, though. – rory.ap Jan 29 '15 at 12:52
@ObsidianPhoenix -- Reading is apparently not my strong point :) I've updated my answer – rory.ap Jan 29 '15 at 12:55
wow wow just collate the column to SQL_Latin1_General_CP1_CS_AS that would do. – AbbathCL Apr 09 '21 at 17:16

Stefan Steiger · Answer 2 · 2022-12-14T09:05:15.017

Instead of making the entire database case-sensitive, just make the primary-key-column case-sensitive:

CREATE TABLE dbo.T_NodeNames
(
     node_name national character varying(20) COLLATE German_PhoneBook_100_CS_AS_KS_WS_SC_UTF8 NOT NULL
    ,tag_name national character varying(20) NULL
    ,tag national character varying(20) NULL
    ,node_type int NULL 
    ,deprecated int NULL
    ,descr national character varying(128) NULL
    ,creation_mechanism national character varying(50) NULL
    ,support national character varying(50) NULL
    ,use_instead national character varying(50) NULL
    ,CONSTRAINT pk_node_names PRIMARY KEY(node_name) 
); 
GO

or as table-variable:

DECLARE @nodeNames TABLE 
(
     node_name national character varying(20) COLLATE German_PhoneBook_100_CS_AS_KS_WS_SC_UTF8 NOT NULL PRIMARY KEY 
    ,tag_name national character varying(20) NULL 
    ,tag national character varying(20) NULL 
    ,node_type int NULL 
    ,deprecated int NULL 
    ,descr national character varying(128) NULL 
    ,creation_mechanism national character varying(50) NULL 
    ,support national character varying(50) NULL 
    ,use_instead national character varying(50) NULL 
);

Make Varchar PK case-sensitive?

2 Answers2