October 23, 2007 at 9:57 pm
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
venkat
October 23, 2007 at 11:42 pm
You can't do that. Here's a quote from Books Online for ALTER TABLE ALTER COLUMN (with my bolding)
ALTER COLUMN
Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).
The modified column cannot be any one of the following:
A column with a timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.
The data type of text, ntext and image columns can be changed only in the following ways:
text to varchar(max), nvarchar(max), or xml
ntext to varchar(max), nvarchar(max), or xml
image to varbinary(max)
Some data type changes may cause a change in the data. For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters. For more information, see CAST and CONVERT (Transact-SQL). Reducing the precision or scale of a column may cause data truncation.
The data type of a column of a partitioned table cannot be changed.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 23, 2007 at 11:43 pm
What happens if you remove the primary key of this table, change the column that you want to the new data type, then finally add the primary key again.
Good Luck!
Osama
October 23, 2007 at 11:51 pm
ok thank u osama . i am getting this ans .
regards
venkat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply