Hello all,
I had to alter a SQL Server database table for my web project. I had to alter the data type of the primary key of the related table from smallint to int.
Running simply the following t-sql statement failed,
ALTER TABLE SampleTable ALTER COLUMN PrimaryKeyField int
since the MS SQL Server Engine throw the following exception with error message
The object 'PK_SampleTable' is dependent on column 'PrimaryKeyField'.
ALTER TABLE ALTER COLUMN PrimaryKeyField failed because one or more objects access this column.
So I had to drop the Primary Key constraint on the table and alter the column type of the fields used in the primary key, then re-create the primary key again on the same columns as follows
GO
ALTER TABLE SampleTable DROP CONSTRAINT PK_SampleTable
GO
ALTER TABLE SampleTable ALTER COLUMN PrimaryKeyField int
GO
ALTER TABLE SampleTable ADD CONSTRAINT PK_SampleTable PRIMARY KEY (PrimaryKeyField)
GO
I hope, this will help you in case you need to change or alter primary key columns/fields or drop primary key constraints on sql database tables.