Primary Key on Non-Clustered Index

Question

Hi,

We already have an unique clustered index on a table1 within that we have one null column, which restrict us to include that as primary key.

And as a standard we would like to have primary key for the table we created, And it was created by business analyst long back.

Now i would like to include Table1ID as identity column and make it as Primary key, But we already have millions of records in the table.

Now the question is,How do i alter the table without affecting the data and create non clustered primary key on newly included ID as we have 1 clustered index on the table.

Regards,

Navin

---

Navin.D http://dnavin.wordpress.com

Answer 1

The primary key does not have to be the clustered index.  If your table does not currently have a primary key, you can add one.  It just takes an ALTER TABLE statement.  So something like

ALTER TABLE <your table name> WITH CHECK 
ADD CONSTRAINT <the name you want for the primary key constraint> PRIMARY KEY NONCLUSTERED (<column(s) you want in the primary key>)

Tom

Answer 2

Hi,

As existing table has already data don't we need to create identity rows for old records, We need to create temp table load data from old table, drop old table create new table  and load data from temp table,that's right approach i guess. Just by altering the table it does not work that way that's what i feel.

Rgards,

Navin

---

Navin.D http://dnavin.wordpress.com

Answer 3

Try the below:

Few things:

1. Identity column will be created as last column.(I believe it should not be concern)

2. The values for identity column may *not* be in an expected way of order for old entries(I dont think you have business here with the new column).

Create Table T1(Col1 int,Col2 int)
Insert into T1 Select 1,1
Insert into T1 Select NULL,2
Insert into T1 Select 2,2
--Unique clustered index
Create clustered index UX_T1_Col1 On T1(Col1)
Alter table T1 add  Col_Identity int Identity(1,1)
Alter Table T1 Add constraint IX_PRimary Primary key  (Col_Identity)

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 4

Hi,

We already have an unique clustered index on a table1 within that we have one null column, which restrict us to include that as primary key.

I assume that you have a unique constraint and with that you can create primary key as clustered or non clustered..you can also use a row number function to update your new column and then create a non clustered index on that or a primary key

---

Thanks and regards, Rishabh K

Answer 5

If you add an identity Column, the old data will be updatetd, too. If you want the new column be the PK it can't be NULL. The old data must have a unique value in the new column. How to create a nonclusterd PK Tom mentioned before.