CODEWITHSUNDEEP
sql-serveralter
Cute Bear
Cute Bear

Reputation: 3293

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

Upvotes: 1

Views: 6551

Answers (3)

Serj Sagan
Serj Sagan

Reputation: 30208

If your constraint is on a user type, then don't forget to see if there is a Default Constraint, usually something like DF__TableName__ColumnName__6BAEFA67, if so then you will need to drop the Default Constraint, like this:

ALTER TABLE TableName DROP CONSTRAINT [DF__TableName__ColumnName__6BAEFA67]

For more info see the comments by the brilliant Aaron Bertrant on this answer.

Upvotes: 0

Venaikat
Venaikat

Reputation: 197

Try this ,

as you told you are getting primary key constraint error , 1st you have to drop the primary key and use the following query ,

    ALTER TABLE SystemDepartment MODIFY DepartmentCode int(3)

Thanks,

Venkat.

Upvotes: -1

sqlhdv
sqlhdv

Reputation: 254

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.

Upvotes: 6

Related Questions