In this post, I will tell you how to change primary key column for existing table in MSSQL 2014. First, you must create table User_Details with the code below:

CREATE TABLE [dbo].[Users_Details](
            [Username] [nchar](20) NOT NULL,
            [Password] [nchar](20) NOT NULL,
            [Email] [nchar](30) NULL,
            [Mobile] [nchar](15) NULL,
            [Address] [nchar](100) NULL,
            [USER_ID] [int] IDENTITY(1,1) NOT NULL,
            [Gender] [varchar](15) NULL,
            [Country] [varchar](50) NULL,
    CONSTRAINT [PK_Username] PRIMARY KEY CLUSTERED
    (
            [Username] ASC
    )
    )

And the change the column as
ALTER TABLE Users_details
ALTER COLUMN USERname NVARCHAR(100)

Now, Run it. It will show the error. Because Username column contains Primary Key.
Msg 5074, Level 16, State 1, Line 1
The object 'PK_Username' is dependent on column 'Username'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Username failed because one or more objects access this column.

First Drop the Primary Key Constraint and alter table and add  Primary Key Constraint
ALTER TABLE Users_Details
DROP CONSTRAINT PK_Username
ALTER TABLE Users_Details
ALTER COLUMN Username NVARCHAR(100) NOT NULL
ALTER TABLE Users_Details
DD CONSTRAINT PK_Username PRIMARY KEY(Username)

Hope this tutorial works for you!

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.