Geek Answers Handbook

Add primary key to table with existing clustered index

I need to work with a database to create database reports rather large: 416 055 104 lines Each line is very light, but only logical and int id.

Each row is identified by three columns, but, to my surprise, it does not have a Primary Key. Only a clustered index with a unique constraint.

I know that I have 2 questions.

  • Could there be ANY reason?
  • Is there a way to turn this into a primary key.

Regarding Question 2

Creating a new primary key also creates a non-clustered index for communication (a clustered one already exists).
This is not what I am looking for. I want to keep the same index, but also make it primary.

  • Is it possible?
  • Would it be faster if you re-create the entire index? (I hope so)
  • What could be the consequences? (blocks? data corruption failure?)
+4
source share
3 answers

The difference between the PRIMARY KEY and UNIQUE constraint for nullable columns is small or nonexistent. Therefore, if the columns in question are not NULL, I suggest you do nothing. The main reason for entering a candidate key in the primary key is if you have some kind of software (for example, a data modeling tool or other development tool) that expects the key to be identified with a PRIMARY KEY constraint.

+1
source

  • A unique index can be null. The primary key cannot.

  • I believe that you cannot “mark” an existing index as a primary key. You will have to drop it and recreate it. To avoid this, I would say that it would be convenient to place TABLOCKX, HOLDLOCK on the table before doing this.

+1
source

Good question.

If you already have a unique index for nullable columns, you have a candidate key. I don’t know of any particular benefit of making this an “official” primary key. In fact, I have a feeling that not turning it into a PC will give more flexibility.

+1
source

More articles:


All Articles