July 22, 2013 at 10:32 am
Using SSMS 2008 R2. This is driving me nuts. I have a table that has a LOT of foreign keys. 2 fields are in need of having their data types changed from nvarchar(18) and nvarchar(35), respectively. When I change them to nvarchar(MAX), I can save the table w/o a problem, but when I go in and edit a row (adding characters), it gives me an error that says "String or binary data would be truncated". Why?
July 22, 2013 at 1:01 pm
I can't help with your error message but I can say that nvarchar(MAX) would be a very unusual choice for a foreign key. So you key field contains more than 4000 characters? Your join performance will suffer. Can you size the columns properly?
July 22, 2013 at 1:11 pm
I agree with Chrissy. If you need varchar(max) as a foreign key you have some very serious design issues. If you want some actual help you will need to post ddl, sample data and a description of what you are trying to accomplish.
_______________________________________________________________
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/
July 22, 2013 at 2:42 pm
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?
July 22, 2013 at 2:48 pm
nvarchar(MAX) is permissible for a foreign key, just not recommended.
Can you provide CREATE TABLE statements for the two tables in question?
How are you trying to change the column length, SSMS or an ALTER TABLE Statement?
Are you getting an error? What is it?
July 22, 2013 at 2:53 pm
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/
July 22, 2013 at 3:05 pm
I realize that my example above is not really accurate because once a foreign key is established you can't change the datatype and the datatypes must match when creating the key. I was trying to illustrate the point.
Of course if we had ddl to work with this would be a LOT easier.
_______________________________________________________________
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/
July 23, 2013 at 8:28 am
Sean Lange (7/22/2013)
I realize that my example above is not really accurate because once a foreign key is established you can't change the datatype and the datatypes must match when creating the key. I was trying to illustrate the point.Of course if we had ddl to work with this would be a LOT easier.
My guess is that the OP is trying to change the datatype with the designer GUI in SSMS, so that when he saves the table, the attempted modification fails because the datatype of a column that participates in a foreign key relationship can't be changed. The datatype stays the same, so the truncation errors occur when trying to insert longer strings. My SSMS (ver. 11.0.3350.0) pops up a bunch of error warnings when I try to save a table modification like this, though. OP, can you describe how you are changing the datatypes?
Jason Wolfkill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply