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

    Change Length of Field

    Hyy,

    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
    Location
    out on a limb
    Posts
    12,410
    drop the RI constraint
    then modify both columns to be the same size and type
    re create the RI constraint
    https://www.google.co.uk/#hl=en&suge...w=1920&bih=995
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    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.

    Thanks

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
  •