Changes to primary key set on multiple fields in already existing table

Question

I am using sql server 2012 enterprise version..

I have got table structure like below.. I need to remove the primary key and need to add primary key to the first column it self

and the table structure is given below ..

 CREATE TABLE [dbo].[ActivationInfo](
    [cert_id] [int] NOT NULL,
    [ActivationKey] [varchar](1800) NULL,
    [Activated_ts] [datetime] NULL,
    [Activated_by] [int] NOT NULL,
    [FLAGGED] [varchar](20) NULL
) ON [PRIMARY]

how can i remove the primary key that is already set on multiple fields after that i need to put primary key only for cert_id

would any one please help on this..

Answer 1

This is pretty simple. Duplicate of Change Primary Key to Composite Key (Primary Key already exists), just reverse the objective wanted.

ALTER TABLE ActivationInfo 
DROP CONSTRAINT CompositeKeyNameGoesHere, 

ALTER TABLE ActivationInfo 
ADD CONSTRAINT pk_CertID PRIMARY KEY (cert_id)

Answer 2

This is simply achieved by dropping the Primary Key constraint and then adding a new one.

First you need to identify the name of your primary key constraint using the following code:

SELECT CONSTRAINT_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
  WHERE TABLE_NAME = 'ActivationInfo' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Then you simply drop and replace the key:

ALTER TABLE ActivationInfo DROP CONSTRAINT **EXISTING NAME**
ALTER TABLE ActivationInfo ADD CONSTRAINT pk_ActivationInfo PRIMARY KEY (cert_id)

Answer 3

To drop use this construct: (IF u know the tablename)

DECLARE @tablename VARCHAR(100) = 'ActivationInfo'

SELECT 'alter table ' + @tablename
       + ' drop constraint ' + name
FROM   sys.objects
WHERE  type = 'pk'
       AND parent_object_id = Object_id(@tablename) 

To Add Primary Key:

ALTER TABLE [dbo].[ActivationInfo] ADD CONSTRAINT PK_ActivationInfo_cert_id PRIMARY KEY(cert_id)