Menu
How do I create a unique constraint on a varchar field that is case sensitive (SQL Server 2005)?
Currently my constraint looks like this:
alter table MyTable add constraint UK_MyTable_MyUniqueKey unique nonclustered (MyCol) When I try to insert the following two values, I get a "Violation of UNIQUE KEY constraint..." error.
insert into MyTable (MyCol) values ('ABC') insert into MyTable (MyCol) values ('abc') --causes a violation of UNIQUE KEY constraint 'UK_MyTable_MyUnqiueKey' I would like the two differently-cased values to be handled as unqiue. I imagine it will involve the following code, but I do not know how it changes my add constraint syntax.
COLLATE SQL_Latin1_General_CP1_CS_AS 
316
By default MySQL ignores differences in case and trailing spaces on varchar . If you need it to be case sensitive, you can alter the table to be varchar(...) binary .
MySQL is case insensitive by default and normally it is more than enough. However one of my recent projects required a case sensitive varchar column with unique index.
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.
This will change the column to be case sensitive. I don't think there's any change to your constraint...
ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS Any selects or joins on this column will become case sensitive as a result of this operation.
133
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
