alter column length

orderno is varchar(64), i need to make it varchar(100)

mID column is computed - based on a formula which includes orderno
i also have a pk which is based on orderno and one other field

i thought i could do this for the pk:

alter table TABLENAME nocheck constraint PKNAME
alter table TABLENAME alter column orderno varchar(100)
alter table TABLENAME check constraint PKNAME

it fails w/the same error i get without tryng to disable the constraint:

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

What is the correct way to disable that constraint so i can increase the column length?
also, how do i get around that computed column?
LVL 17
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have to drop the PK constraint, change the column, and recreate the PK. no way around that.
dbaSQLAuthor Commented:
yeah, that's what i figured.  i just wanted to make sure there wasn't some magic piece i wasn't aware of.  what of the computed column, angelll?  do you know how i get around that?
dbaSQLAuthor Commented:
does the computation have to be dropped?  and even so, how would i do that?  it's created at table creation, like this:

create table TABLENAME (
 mID AS (orderno + '.' + convert(varchar(25),timestampdate,121)),

can you advise, angelll?
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, the computed column has to be dropped also and added afterwards
dbaSQLAuthor Commented:
eeewgh... yuck
i need to drop the computed column, then drop the constraint,
alter column length
add the computed column back (with formulat in place)
recreate the constraint

problem number 1, i don't want to get into my syscolumns to get mID back up to the 1st column in the table.  well, i do want to, actually  ....i'm just not certain i have the best means of doing so

---allow updates
---add the column, with the formula
---update syscolumns, changing colid and colorder for new column
---disallow updates

is that it?  do you think i've missed anything?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if there is any trigger on the table... or any view on the table, you need to recompile those...
dbaSQLAuthor Commented:
no triggers, there is one view.  ugh
dbaSQLAuthor Commented:
--allow updates
--drop column, drop pk constraint
--adjust length of orderno column
--recreate mID column, with the formula
--update syscolumns, changing colid and colorder for new column
--recreate pk
--disallow updates
--exec sp_recompile viewname

you think that's it, angell?  i gotta do this on 11 different instances tonight after production orderflow ceases.  i need to be sure i'm not forgetting anything
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to be 100% sure, backup/restore one of the databases, and try to run that as script there...
that way, you will be sure.
dbaSQLAuthor Commented:
yep.  agreed completely
i'll let you know how it goes after the fact

thank you, angelll
All Courses

From novice to tech pro — start learning today.