Specifying existing non clustered unique index when defining a primary key constraint

Question

I have a heap table - no clustered index defined - (call it table A), with a unique non clustered index on a non nullable column (call the column ID and the index IX).

I would like to use index IX when defining the primary key constraint on column ID for table A.

The documentation somewhere says this:

The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

I've read through the entire ALTER TABLE documentation but there seems to be no syntax for "nonclustered index is ... explicitly specified, ".

Have tried defining the nonclustered index IX and specifying primary key, and have also tried various combinations of the alter table ... add constraint ... primary key statement to no avail.

It makes sense that my index IX is equivalent to the nonclustered index that SQL Server creates when I simply specify the ID column in the alter table .... add constraint .... primary key (ID) statement, but I would prefer not having this redundant index which SQL Server creates for me, and rather make it use the index IX which also consists of a include list of columns.

If I drop the index that SQL Server creates then the primary key constraint also vanishes.

If it were possible to alter the index that SQL Server creates my problem would be solved, but the alteration I would like to do to it requires a drop and recreate.

Why not just leave the table as it is? A column with NOT NULL and UNIQUE constraints is equivalent to a column with PRIMARY KEY constraint. Why change it anyway? — ypercubeᵀᴹ
The documentation is not clear. I think it means that either a clustered or nonclustered index is created, depending on the statement. Not that you can specify a previously existing index. So, you cannot avoid creating a new index when defining a PRIMARY KEY constraint. — ypercubeᵀᴹ
My guess is they are referring to the optional [ CLUSTERED | NONCLUSTERED ] part that follows PRIMARY KEY in the primary key declaration (which is probably what @ypercube is talking about too). The syntax diagrams show no way of specifying an existing index to use with the primary key constraint, you can only specify whether the [new] primary key index is going to be clustered or non-clustered. — Andriy M
Cannot leave the table as it is - require a primary key in order to have foreign key constraints on other tables, referencing the table in question. Can also not just let sql server do it's thing and create the index since the auto created index is not exactly what is required - would like to, for example, have a few include columns on the index. For now I'm settling for just having the redundant index created. This is not ideal since it is one more index to be maintained during DML statements, and one more index to confuse the query optimiser :-), and a bit more memory wasted. — Kerneels Roos
yes Andriy M , I agree, they probably do refer to the ability to specify NONCLUSTERED INDEX or not - as aposed to specifying an actual existing index :-) but imo, they should rephrase. Thanks though. — Kerneels Roos

Martin Smith Martin Smith · Accepted Answer · 2014-12-28T13:22:06

There is no way to create a constraint and associate it with an existing index that already guarantees the constraint.

This functionality does exist in other RDBMS. It would be particularly useful for the supertype/subtype pattern as this requires creating unique indexes on both Id and (Id, Type) even though the latter one (required for the FK) is logically ensured by the first.

It is possible to replace the Unique index with a Unique constraint as a metadata only change using ALTER TABLE ... SWITCH but attempting to do the same with a nonclustered PK constraint fails with

ALTER TABLE SWITCH statement failed. There is no identical index in source table 'A' for the index 'IX' in target table 'B'.

The code that performs this for a unique constraint is

Initial Position

CREATE TABLE dbo.A(ID INT NOT NULL, OtherCols VARCHAR(200));

CREATE UNIQUE NONCLUSTERED INDEX IX ON dbo.A(ID);

INSERT INTO dbo.A VALUES (1,'A'),(2,'B');

Replace unique index with unique constraint

SET XACT_ABORT ON;

BEGIN TRAN;

CREATE TABLE dbo.B
  (
     ID        INT NOT NULL CONSTRAINT IX UNIQUE NONCLUSTERED,
     OtherCols VARCHAR(200)
  );

ALTER TABLE dbo.A 
      SWITCH TO dbo.B;

DROP TABLE dbo.A;

EXECUTE sp_rename
  N'dbo.B',
  N'A',
  'OBJECT';

COMMIT;

Specifying existing non clustered unique index when defining a primary key constraint

1 Answers

Initial Position

Replace unique index with unique constraint