Search code examples
sql-serveralter

Change datatype of a column and set a column as identity in SQL

----------------------------------------------
DepartmentCode  varchar(30) AllowNulls
----------------------------------------------

Does anyone know how to change the datatype of a column in SQL 2008? This is the column I want to alter but when I try this query,

ALTER TABLE SystemDepartment ALTER COLUMN DepartmentCode smallint NOT NULL

I get the following error:

Msg 5074, Level 16, State 1, Line 1 The object 'PK_SystemDepartment' is dependent on column 'DepartmentCode'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN DepartmentCode failed because one or more objects access this column.

My question is how to force my query to cope with it? and I also would like to set this column as primary key and identity

Solution

  • You will first need to drop Primary Key constraint.

    ALTER TABLE SystemDepartment DROP CONSTRAINT PK_SYSTEMDEPARTMENT

    Then only you can ALTER that column.

    You can not force existing column to identity. In this case you will need to add new column with identity and then do sp_rename to old name.