Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dumb question about data types


Dumb question about data types

Author
Message
chugghin
chugghin
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 196
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?
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4575
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?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16568 Visits: 17016
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)
chugghin
chugghin
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 196
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?
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4575
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?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16568 Visits: 17016
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16568 Visits: 17016
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)
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search