Add Primary Key to a table with existing clustered index

Question

I have to work with a database to do reporting The DB is quite big : 416 055 104 rows Each row is very light though, just booleans and int ids.

Each row is identify by 3 columns, but at my surprise, there is no Primary Key on it. Only a Clustered Index with a unique constraint.

So Knowing that, I have 2 question.

1. Could there be ANY good reason for that?
2. Is there any way I can turn this into a primary key.

Regarding question 2

Creating a new primary key also creates a non-clustered index to associate with (there is already an existing clustered one).
This is not what I am looking for. I want to keep that same index, but also make it a primary key.

• Is it possible?
• Would that be faster that creating the whole index again? (I hope so)
• What could be the consequences? (locks? crash? corrupted data?)

Answer 1

1. A unique index can allow null values. A primary key can't.

2. I believe you can't "mark" an existing index as the primary key. You'd have to drop it and recreate. To avoid stuff, I'd say it'd be good to place a TABLOCKX, HOLDLOCK on the table before doing that.

Answer 2

Good question.

If you already have a unique index on non nullable columns then you have a candidate key. I'm not aware of any particular benefit of making this an "official" primary key. In fact I have a feeling that not making it a PK will give greater flexibility.

Answer 3

There is little or no difference between a PRIMARY KEY and a UNIQUE constraint on non-nullable columns. So if the columns in question are non-nullable then I suggest you do nothing. The main reason to make a candidate key into a primary key is if you have some software (such as a data modelling tool or other development tool) that expects the key to be identified with a PRIMARY KEY constraint.