开发者

Add Primary Key to a table with existing clustered index

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开发者_Go百科 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?)


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.


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.


  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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜