Delete or change the primary key constraint to UNIQUE constraint using SQL Server 2008

Question

I need to know if there is any way I can change or drop the primary key constraint of a table and replace it with a UNIQUE constraint.

When I try to drop the primary key constraint from my Company table:

ALTER TABLE Company DROP CONSTRAINT PK__Company__AABA1D8F1B0907CE;

I get this error:

Msg 3725, Level 16, State 0, Line 1 The constraint 'PK__Company__AABA1D8F1B0907CE' is being referenced by table 'Project', foreign key constraint 'FK_Project_Company'. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors.

The problem is that I don’t want to delete any rows in the Project table.

Here’s a simplified version of my schema:

CREATE TABLE Company (
    CompanyID INT,
    CompanyName VARCHAR(100),
    CompanyCode VARCHAR(20) UNIQUE,
    Address VARCHAR(100),
    UniqueNumber INT UNIQUE,
    Status VARCHAR(20),
    EmployeeCount INT,
    City VARCHAR(20),
    PrimaryContact VARCHAR(50),
    EmailContact VARCHAR(50),
    PhoneContact VARCHAR(20),
    BankAccount VARCHAR(60),
    GroupName VARCHAR(50) FOREIGN KEY REFERENCES Groups(GroupName),
    PRIMARY KEY (GroupName, CompanyID)
);
GO

CREATE TABLE Project (
    ProjectID INT PRIMARY KEY,
    CompanyID INT,
    SubmissionDate DATETIME,
    ProjectType VARCHAR(2),
    Status VARCHAR(20),
    Budget DECIMAL(10,2),
    Duration INT,
    GroupName VARCHAR(50),
    CONSTRAINT FK_Project_Company 
        FOREIGN KEY (GroupName, CompanyID) 
        REFERENCES Company(GroupName, CompanyID)
);
GO 

Note: Schema has been simplified and anonymized for security reasons

How can I safely change the primary key to a UNIQUE constraint without breaking the foreign key relationship in the Project table?

Answer 1

You cannot do the "change" automatically, with a single SQL instruction, but you can achieve that if you want to.

First, you need to drop the foreign-keys of those tables containing references to the referenced table, Enterprise, in your concrete case.

You need to drop the foreign-key from Dossier, then drop the primary-key from Enterprise, and create a UNIQUE constraint.

Another question would be, why are you interested on doing that?

Maybe you can read this other SO thread discussing about the matter.

Answer 2

As the error is suggesting, you need to delete the foreign-key reference first. This will not delete the records in Dossier (see my SQL Fiddle example.) :

ALTER TABLE Dossier DROP CONSTRAINT Cle_FDOs;