chugghin (7/22/2013)
Well, I tried setting it up for a test. The field is limited to 18 characters, but I can't even set it to 50 or 25...basically, can't go one character over 18. I was just wondering if FKs have a limit by default or can they be set someplace else?
Foreign keys don't have a datatype. They simply say that such and such value must exist in the other table. They reference columns in tables and those columns have datatypes. I have a feeling that given the nature of your issue you have your foreign keys setup as cascade on update? This is not a bad thing but if the datatypes in the referred columns are unable to hold the data you will get an error.
In other words, you have a column in TableA with a datatype of varchar(10). In tableB you have a foreign key pointing to Column1 and the datatype is varchar(10) and on UPDATE CASCADE.
Now you go and change the datatype of your column in TableA to be varchar(100) but you don't update the column in TableB. If you update the column in TableA with 10 characters or less it will be fine. But as soon as you update to 11 or more characters you will get the error you say you are getting.
As previously stated if you need keys that are varchar(max) you have some really serious architecture issues that need to be sorted out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/