0

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?

Alexander
  • 19,443
  • 17
  • 61
  • 146
  • 1
    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

1 Answers1

1

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

rory.ap
  • 32,474
  • 10
  • 77
  • 158
  • 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