question

RobertGustafson-1682 avatar image
0 Votes"
RobertGustafson-1682 asked RobertGustafson-1682 edited

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

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.




sql-server-generaldotnet-visual-basicwindows-formsdotnet-entity-framework
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered RobertGustafson-1682 edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can I do this when generating the database from the model? Remember, I'm doing this Model-First. I also would like to do as little raw SQL as possible. As I mentioned above, I'm using Entity Framework and prefer its OOP approach. I don't want to have to use a lot of SQL strings in my program code (any more than necessary), or make some non-program-code modifications that have to be repeated every time I build the database after changes to the model. Also, remember that the unique index may not be the primary key, but a secondary one created by program-code SQL strings executed on the store. (I've developed a tool for easily converting between conceptual-model and store-model info, which I intend to eventually publish on CodeProject.)

0 Votes 0 ·
DanGuzman avatar image DanGuzman RobertGustafson-1682 ·

I code-first in newer EF versions allows you to decorate your class to create the unique index (e.g. [Index(nameof(EMailAddress), IsUnique = true)]). However, with your custom tooling, you'll need to create and execute the needed DDL from your model. Examples.

 --unique index
 CREATE UNIQUE NONCLUSTERED INDEX idx ON dbo.Example (EMailAddress);
 --unique constraint
 ALTER TABLE dbo.Example
     ADD CONSTRAINT UQ_Example_EMailAddress UNIQUE NONCLUSTERED(EMailAddress);

As already mentioned, collation is case-insensitive by default unless you specified otherwise when installing SQL Server database engine.

0 Votes 0 ·

Will the SQL case-sensitivity settings for my server also apply for all end users who install a published version of my app?

BTW, my program does execute the (non-primary-key) index-creation SQL at run-time, using a tool I developed that lets me readily get store info (table/column) from conceptual-model info (entity/property-chain). (I'll eventual publish the tool on CodeProject.)

In order to force case-insensitive collation, do I need to modify the DDL files (re-)created by EF when I (re-)build the database from the model, and is there a way to ensure, aside from run-time ALTER TABLE stuff (seems a bit tedious), that I don't have to do this again and again with every database rebuild?


0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.