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

Question

----------------------------------------------
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

To add to the responses below, first you need to get the ID of the PK. This is how I do it: SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'SystemDepartment'; That returns the ID of the PK which you can then use as detailed by Hardik below. — Tim Bostwick, Aug 12 '14 at 23:50

score 6 · Accepted Answer · edited Sep 03 '12 at 08:40

6

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.

edited Sep 03 '12 at 08:40

Himanshu

31,810
31
111
133

answered Sep 03 '12 at 08:39

sqlhdv

254
1
7

You can alter an existing column to be `IDENTITY` using [`ALTER TABLE ... SWITCH`](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661) but couldn't do the datatype change from `varchar` to `int` without dropping the PK. – Martin Smith Sep 03 '12 at 08:58

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

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.

edited May 23 '17 at 12:07

Community

1
1

answered Mar 16 '13 at 06:58

Serj Sagan

28,927
17
154
183

score -1 · Answer 3 · answered Sep 03 '12 at 09:08

-1

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.

answered Sep 03 '12 at 09:08

Venaikat

197
2
5
20

That is not valid SQL Server syntax. – Martin Smith Sep 03 '12 at 09:11
am apolozige for my mistake , please try this. it will work for sure, ALTER TABLE dbo.emp alter column emp_id int – Venaikat Sep 03 '12 at 09:21

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

3 Answers3