If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > Change Length of Field

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Dec 2012
Posts: 2
Change Length of Field


I have a database on sql2000. I want to change the length of a field in Table A, but it is related to another field in Table B. When I tried to change the length of the field in Table A via

ALTER TABLE table ALTER COLUMN field varchar(5)

The query analyzer generates the following error.

Server: Msg 5074, Level 16, State 8, Line 1
The object 'UQ__table__77BFCB91' is dependent on column 'field'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN field failed because one or more objects access this column.

While when I tried the same query on the field in TABLE B, the query completed without any errors.

I have no clue as to what to do?

Any help is appreciated
Reply With Quote
  #2 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,289
drop the RI constraint
then modify both columns to be the same size and type
re create the RI constraint
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:-
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Dec 2012
Posts: 2
thanks healdem, for your reply, Healdem, I used the query "select * from information_schema.table_constraints", which showed me, the list of all the tables with constraints, and it also had the table which I was targeting to change the field length of. But in the constraint type column that particular table showed unique. all of the other showed primary key, which I didnt know what to do with. Secondly, following the link you provided in your reply, i dropped the constraints some how, and was able to change the length of the field from varchar(4) to varchar(30). But after changing the field length, when I tried to enter more than 4 characters, sql generated an error saying "String or binary data may be truncated. The statement has been terminated."
What should I do? I have no clue what so ever.
I have a backup copy of the database, therefore I can always start with a fresh copy again. But what to do. please enlighten me.

Reply With Quote

database field length, field length, sql2000

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On