?
Solved

Foreign keys case-insensitive?

Posted on 2004-10-29
6
Medium Priority
?
786 Views
Last Modified: 2011-10-03
I have a state table with primary keys like AK, AL, etc. (state abbreviations)  I have another table with a column that references it, but there's entries in the column like Ak, aL, etc.  What's up with that?  I thought foreign key relationships validated exact matches.  Is there a flag or setting in SQL Server to make foreign key relationships case-sensitive?
0
Comment
Question by:rramaiah
  • 2
  • 2
  • 2
6 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12448050
Not unless the instance itself is case sensitive.  Best might be to add a CHECK to the table to verify that only upper case values are stored in it:

CHECK(ASCII(LEFT(stateColumn, 1)) BETWEEN 65 AND 90 AND ASCII(SUBSTRING(stateColumn, 2, 1)) BETWEEN 65 AND 90)
0
 

Author Comment

by:rramaiah
ID: 12448106
Scott,
What do you mean by "Not unless the instance itself is case sensitive".  Are you referring to the database instance?  Is there an option in the database to make it case sensitive?  It seems odd to have to add a foreign key AND a check constraint to get it to do this.
0
 
LVL 8

Accepted Solution

by:
sigmacon earned 500 total points
ID: 12448199
If you are using MS SQL 2000, You can make columns case sensitive by specifying a "collation":

alter table SomeTable
    alter column SomeColumn varchar(100) collate Latin1_General_BIN -- binary collation

I copied the following from my response to http:Q_21186541.html

A collation determines not only case-sensitivity or insensitivity, but also how something is sorted. There are two fundamental sets of collations, original SQL Server collations, which existed since SQL Server 7.0 and the Windows-compatible collations. It is interesting to note that the selected collation influences string comparison, and as such index construction and performance. Be careful with changing collations. Not many users seem to be fond with that and you can quickly run in collation conflicts when you are dealing with more than one database or more then one server. The collation for an entire server can be changed with sp_configure.

BTW: AS stands for Accent-sensitive, AI would be accent-insensitive. The difference in collations has more pronounced performance impact for nvarchar columns, because comparison in Unicode is a complex issue.

Also, in a foreign key relation, both columns will need to have the same collation.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:rramaiah
ID: 12448272
Thanks for the prompt and detailed response!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12448295
>>  Are you referring to the database instance? <<

Yes.  You can choose to make the instance case-sensitive when you install SQL.


>>  Is there an option in the database to make it case sensitive? <<

In theory.  But it's *very* tricky once the db has already been created on a case-insensitive instance.  I've never known anyone who actually got that to work really properly.
You can try sigmacon's changes, but allow yourself plenty of time for debugging and testing -- very thorough testing.


>> It seems odd to have to add a foreign key AND a check constraint to get it to do this. <<

Not necessarily.  Remember, when you installed SQL you told it that you wanted it to be case insensitive.  It's simply following that rule.
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12448907
Scott's is correct about the Collation issue always being tricky. The only reason why I personally am comfortable with is because I HAD to use it quite a lot to control how things are sorted or indexed in text columns. All kinds of collation-combatibility issues WILL arise - within a table, between tables, between DBs and even across servers. Be warned!

One more thing. I try to use natural keys when possible. There are many different thoughts about this, and often the argument against natural keys is performance. I have compared index performance for integer columns with small varchar columns using the binary collation Latin1_General_BIN, and found NO performance difference. So its safe to assume that chosing the right collation does make a difference. There are many real-life applications that require case-sensitivity, so getting accustomed to this may not be bad.

Caution, if you create an entire DB to be case sensitive, then EVERYTHING in that db is case sensitive, table names and so on included. If your entire SERVER INSTANCE is case sensitive, then you are going to have lots of fun with other people's scripts that are written on a case-insensitve system, because even if you create a case-insensitive DB on a case-sensitive instance, all DDL statements are still case sensitive.

For the latter reason I run two instances on my development system. One case-sensitive, for all the stuff where I have the last word. And one case-insensitive for the stuff where I am collaborating with others.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question