Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dumb question about data types Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 28, Visits: 139
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?
Post #1476132
Posted Monday, July 22, 2013 1:01 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 653, Visits: 3,904
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?
Post #1476220
Posted Monday, July 22, 2013 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476225
Posted Monday, July 22, 2013 2:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 28, Visits: 139
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?
Post #1476275
Posted Monday, July 22, 2013 2:48 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 653, Visits: 3,904
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?
Post #1476278
Posted Monday, July 22, 2013 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476281
Posted Monday, July 22, 2013 3:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 PM
Points: 13,078, Visits: 12,529
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476285
Posted Tuesday, July 23, 2013 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 1,061, Visits: 2,572
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1476611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse