swamym


I Have table Emp contains 3 columns one column is EName that datatype is char. now I want to change char to Varchar(4) . but column is primary key . and it is refernced by othertable columns .


i write query
alter table emp alter column ename varchar(4)
but it throws an error is
Msg 5074, Level 16, State 1, Line 1
The object 'PK_emp is dependent on column 'Ename'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ename failed because one or more objects access this column.

please help me
asap.
Regards
Swamy




Re: how alter only datatype for existing column

DarrenSQLIS


Please try and select the correct forum. I am moving this thread.

You will need to remove teh constraints, make the change and then re-apply the constraints. If this takes part in foreign key relationships, then those columns will need to be changed to match the type.

There is no magic way of doing this in one statement, but it is not too hard either.







Re: how alter only datatype for existing column

Frank Kalis

swamym wrote:
I Have table Emp contains 3 columns one column is EName that datatype is char. now I want to change char to Varchar(4) . but column is primary key . and it is refernced by othertable columns .


i write query
alter table emp alter column ename varchar(4)
but it throws an error is
Msg 5074, Level 16, State 1, Line 1
The object 'PK_emp is dependent on column 'Ename'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ename failed because one or more objects access this column.

please help me
asap.
Regards
Swamy

Why do you want to change to VARCHAR(4) I don't think there will much of a gain anyway.





Re: how alter only datatype for existing column

zuomin

Normally, you won't have performance gain by change char to varchar(4).

Because varchar will take 2 more char than actual length for storage. And it will also truncate the char longer than 4.

So if most of your data take up 3 or 4 chars. varchar(4) will take up more space. Then more IO.

So actually it's not recommend to use varchar for such a small size. especially for those data about the same size.


Thanks,
Zuomin




Re: how alter only datatype for existing column

Tom Phillips

To answer your question, you first have to drop the PK to change the datatype.