Is there a way to create a unique index with 1 or more string fields case-INsensitve?

Robert Gustafson 606 Reputation points
2022-05-18T02:17:16.037+00:00

WHAT I HAVE:
Visual Basic 2019+, .NET Framework 4.6+, Entity Framework 6+, SQL Server ??.??, WinForms

MY ISSUE:

What if I wish to create a unique index (not necessarily the primary key, but it can be) with 1 or more string fields checked an a case-insensitive, rather than exact-match, basis? For instance, can I make it so that if there's an entry "RobertAG2" and the program tries to insert "ROBERTAG2" or "robertag2"--which differ from the existing entry only in case--an exception is thrown, just as if they were identical? That be useful for, say, email and URL addresses--which are found by browsers on a case-insensitive basis (??), but for which the user might have a casing preference for display purposes. It would be tedious to have an index field with a general (say, all-lowercase) casing and another, non-index one with the preferred case--particularly if there are several fields in several tables with that need,--because that would be a lot of de facto duplication (a database no-no), and it means a bug in the program--or possible alteration by another program--could result in situations where a pair of fields don't differ only in case--causing the program to malfunction. Yet the SQL to create a unique index that's mentioned in help doesn't seem to have a clause for case-sensitivity status for string fields.

Is there a solution? If so, I want it in VB.NET, using minimal changes in the raw SQL syntax for unique-index creating (I prefer the OOP style of EF--complete with Intellisense,--given a choice), and as simple as possible. Please answer ASAP.

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,811 questions
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,264 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,542 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 40,156 Reputation points
    2022-05-18T10:13:08.8+00:00

    Is it a (or more) column of type varchar = ASCII?
    Then define a case-insensitive (CI) collation like Latin1_General_CI_AS

    See https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15

    0 comments No comments

  2. Dan Guzman 9,201 Reputation points
    2022-05-18T10:36:01.7+00:00

    The column collation determines sorting and comparison rules for character data, including case, accent and kana sensitivity. The column collation is honored for indexes so a unique index or constraint on a column with a case-insensitive collation (e.g. Latin1_General_CI_AS), values that differ only by case will be considered duplicates. For Example:

    CREATE TABLE dbo.Example (  
     EMailAddress varchar(255) COLLATE Latin1_General_CI_AS   
     CONSTRAINT PK_Example PRIMARY KEY  
    );  
    INSERT INTO dbo.Example(EMailAddress) VALUES('ROBERTAG2');  
    INSERT INTO dbo.Example(EMailAddress) VALUES('robertag2'); --this insert fails  
    GO  
    

    Your column collation (inherited from the database default collation) may already be case-insensitive. Check the column property in SSMS object explorer or with this query to determine the collation existing column. A collation name with the "_CI" option denotes case-insensitive.

    SELECT collation_name  
    FROM sys.columns  
    WHERE   
     object_id = OBJECT_ID(N'dbo.Example')  
     AND name = N'EMailAddress';  
    

  3. Tom Phillips 17,716 Reputation points
    2022-05-18T12:12:10.47+00:00

    SQL Server by default is case-insensitive. It is very unusual to make it case sensitive. Please post your table definition with the collation of the columns in question.

    0 comments No comments

  4. Erland Sommarskog 100.1K Reputation points MVP
    2022-05-19T06:35:06.467+00:00

    When you install SQL Server, you can select a server collation. The default collation depends on your system locale. For instance with a Swedish system locale, the default will be Finnish_Swedish_CI_AS, which is a case-insensitive collation. (That's what CI stands for.) For other locales, the collation will be different. However, it will always be a CI collation.

    The server collation then serves as the default collation for the database collation when you create new databases. And the database collation in its turn serves as the default collation of columns you create.

    Thus, if you just go with the flow, you will have a case-insensitive collation and there is no need to worry.

    So if you have a CS (case-sensitive) collation in your database, it is because someone deliberately choose that collation. I suggest that you talk to that person why that choice was made. It is still possible to override this choice for specific columns, but maybe that is over the top.

    0 comments No comments