Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012

    Unanswered: 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

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    drop the RI constraint
    then modify both columns to be the same size and type
    re create the RI constraint
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    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.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts