rramaiah
asked on
Foreign keys case-insensitive?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the prompt and detailed response!
>> 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.
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.
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.
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.
CHECK(ASCII(LEFT(stateColu