Change Primary Key to Composite Key (Primary Key already exists)

composite-primary-key, primary-key, sql, sql-server

Solution

    /* For SQL Server/Oracle/MS ACCESS */
    ALTER TABLE  AgentIdentification 
    DROP CONSTRAINT PK_Table1_Col1


    /* For MySql */
    ALTER TABLE  AgentIdentification 
    DROP PRIMARY KEY

To Add primary key :

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Table1
ADD CONSTRAINT pk_PersonID PRIMARY KEY (AgentId, IdIndicator)

Problem

I am trying to change the primary key of a table in my SQL database from the existing key to a composite key, which does not include the existing column. The following code is not working due to the following error messages: DROP PRIMARY KEY: Incorrect Syntax near PRIMARY. Expecting COLUMN, CONSTRAINT, ID, or QUOTED_ID ADD PRIMARY KEY: Incorrect Syntax near PRIMARY. Expecting ID T-SQL code: ``` ALTER TABLE AgentIdentification DROP PRIMARY KEY Number, ADD PRIMARY KEY (AgentId, IdIndicator) ``` EDIT I was able to accomplish this by using the following two query statements ``` ALTER TABLE AgentIdentification DROP CONSTRAINT [PK_AgentId_Id] GO ALTER TABLE AgentIdentification ADD CONSTRAINT pk_PersonID PRIMARY KEY (AgentId, IdIndicator) ``` Instead of requesting that SQL "DROP PRIMARY KEY" I needed to tell it to "DROP CONSTRAINT", also separating these two actions into two queries helped.

Original source