|
|
Error Message
Server: Msg 5074, Level 16, State 8, Line 1
The object 'Primary Key Constraint Name' is dependent on
column 'Primary Key Column Name'.
ALTER TABLE ALTER COLUMN Column Name failed because one
or more objects access this column.
Causes:
This error occurs when changing the data type or length of a column using the ALTER TABLE ALTER COLUMN command and that column is a PRIMARY KEY of the table.
|
To illustrate, let’s say you have the following table definition:
CREATE TABLE [dbo].[Users] (
[UserID] SMALLINT NOT NULL IDENTITY,
[UserName] VARCHAR(20),
[Password] VARCHAR(20)
)
GO
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT PK_Users PRIMARY KEY ( [UserID] )
GO
It was not anticipated during the initial design that the number of users of your application will not exceed 32,767, the maximum value that a SMALLINT data type can hold. But due to its popularity, the number of users of your system has exceeded this number and you need to change the data type of the UserID from SMALLINT to INT.
To change the data type of the column from SMALLINT to INT, you issued the following command:
ALTER TABLE [dbo].[Users]
ALTER COLUMN [UserID] INT NOT NULL IDENTITY
But since the UserID column is the PRIMARY KEY, the following error is encountered:
Server: Msg 5074, Level 16, State 8, Line 1
The object 'PK_Users' is dependent on column 'UserID'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN UserID failed because one or more objects access this column.
Solution / Work Around:
The easiest way to change the data type of a column that belongs to the PRIMARY KEY constraint is to do the changes in Enterprise Manager. Enterprise Manager will take care of recreating the table for you and change the data type of the column that is a PRIMARY KEY.
To do it manually using Transact-SQL statements, assuming that the table is not being referenced by any other table in a FOREIGN KEY relationship, you first have to drop the PRIMARY KEY constraint, then change the data type of the column and lastly create the PRIMARY KEY constraint again.
ALTER TABLE [dbo].[Users]
DROP CONSTRAINT PK_Users
GO
ALTER TABLE [dbo].[Users]
ALTER COLUMN [UserID] INT NOT NULL
GO
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users] PRIMARY KEY ( [UserID] )
GO
If the table is being referenced by other tables in a FOREIGN KEY relationship, you have to follow the following steps:
- Drop the FOREIGN KEY constraints from the other tables.
- Drop the PRIMARY KEY constraint from the primary table.
- Change the data type of the column in the primary table as well as in the other tables involved in the FOREIGN KEY relationship.
- Create the PRIMARY KEY constraint in the primary table.
- Lastly, create the FOREIGN KEY relationships previously dropped.
|