spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Case-insensitive primary key of type nvarchar where ß != ss


The closest I can get to a workaround is using an indexed view to add a unique constraint on UPPER(ID) to stop primary key violation that is not picked up by the binary collation used on the actual primary key, e.g.

CREATE TABLE CollationTest 
(
        ID NVARCHAR(50) COLLATE Latin1_General_BIN
NOT NULL,
    CONSTRAINT PK_CollationTest_ID PRIMARY KEY
(ID)
);
GO
CREATE VIEW dbo.CollationTestConstraint
WITH SCHEMABINDING
AS
    SELECT  ID = UPPER(ID)
    FROM    dbo.CollationTest;
GO
CREATE UNIQUE CLUSTERED INDEX
UQ_CollationTestConstraint_ID 
    ON dbo.CollationTestConstraint (ID);

Now the following will work as the initial insert:

INSERT dbo.CollationTest (ID) VALUES
('weiß');
INSERT dbo.CollationTest (ID) VALUES ('Weiss');

But the following will fail:

INSERT dbo.CollationTest (ID) VALUES
('Weiß');
INSERT dbo.CollationTest (ID) VALUES ('weiss');

Cannot insert duplicate key row in object 'dbo.CollationTestConstraint' with unique index 'UQ_CollationTestConstraint_ID'. The duplicate key value is (WEIß).

Cannot insert duplicate key row in object 'dbo.CollationTestConstraint' with unique index 'UQ_CollationTestConstraint_ID'. The duplicate key value is (WEISS).

This appears to match your criteria after initial testing.

EDIT

I definitely over complicated this, you can achieve the same thing with a computed column and a unique constraint:

CREATE TABLE CollationTest 
(
        ID NVARCHAR(50) COLLATE Latin1_General_BIN
NOT NULL,
        IDUpper AS UPPER(ID),
    CONSTRAINT PK_CollationTest_ID PRIMARY KEY
(ID),
    CONSTRAINT UQ_CollationTest_IDUpper UNIQUE
(IDUpper)
);

Categories : SQL

Related to : Case-insensitive primary key of type nvarchar where ß != ss
How to create a case insensitive Regexp from a non-case insensitive Regexp?
To add the case-insensitive "ignore case" option on the fly: original = /abc/ insensitive = Regexp.new( original.source, original.options | Regexp::IGNORECASE) To make the match case-insensitive without using the "ignore case" option, a possible solution is to create a regexp that matches uppercase letters and lowercase letter like this: original = /abc/ insen

Categories : Ruby
What is the difference between PRIMARY KEY and CONSTRAINT PRIMARY KEY?
I think both primary key definitions are identical. See my sqlfiddle. and output from show create table Employes_1 und _2: Employees_1 | CREATE TABLE employees_1 ( E_Id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (E_Id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Employees_2 | CREATE TABLE employees_2 ( E_Id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (E_Id) ) ENGINE=InnoDB DEFAUL

Categories : Mysql
How to use indexOf() case insensitive
You can Use Overloaded Method IndexOf("punctuation", StringComparison.OrdinalIgnoreCase); Eg. List<string> fnColArr = new List<string>() { "Punctuation", "period", "Space", "and", "yes" }; foreach (string item in fnColArr) { if (item.IndexOf("puNctuation", StringComparison.OrdinalIgnoreCase) >= 0) { Co

Categories : C#
Case-insensitive "contains" in Linq
the easy way is to use ToLower() method var lists = rec.Where(p => p.Name.ToLower().Contains(records.Name.ToLower())).ToList(); a better solution (based on this post: Case insensitive 'Contains(string)') var lists = rec.Where(p => CultureInfo.CurrentCulture.CompareInfo.IndexOf (p.Name, records.Name, CompareOptions.IgnoreCase) >= 0).ToList();

Categories : C#
'which' command in R with case insensitive
You can index it with grepl by using the ignore.case argument x <- c("col7", "COL73", "Col17", "CoL73", "cOl73") grepl("col73", x, ignore.case=TRUE) # [1] FALSE TRUE FALSE TRUE TRUE Similarly, grep returns the numeric index grep("col73", x, ignore.case=TRUE) # [1] 2 4 5 For data frame column subsets df[grepl("col73", names(df), ignore.case=TRUE)]

Categories : R
Recently Add
Use REGEXP_REPLACE to replace variable number of characters with same number of constant characters
How to subtract a data with nano-second in a same group
listagg alternative in Oracle
How to join 3 tables to a single table in SQL
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Data Frame in R use like SQL, possibly using sqldf()
How can I group by a table by the date?
Query slows down in Conditional IF
Getting the daily sales report given the date
Retrieve rows with unique values in a column
case sensitive sql search in vb.net
Oracle multi dimensional query
Update column when datediff is greater then other column in the same table
add value from different tables and insert total value into another table
How to prevent transaction locking in sql server for all the connections?
Check the query efficiency
how can I run this select statement inside a select statement without any problems?
Add amount once on duplicate entries
SQL Server 2012 - Unique Priority Column
Query: employees who do not have the same name
Trigger to not allow a member to rent if unpaid balance exceeds $50
Anyway way to change this Oracle SQL statment to use regs?
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.