Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

How to Creating Primary Key On SQL Server Table

I am using SQL Server Developer 14.  I am pretty new to SQL Server.

I was working on the indexes for a table in the SQL DB.  I added a new index and wanted to delete one that was no longer needed.  Instead of deleting the index I intended, I deleted the PrimaryKey index,, which was on the Indentity column (PayRecID) of the table.

I recreated a clustered, unique index to replace the Primary Key index but I'm not sure if that is sufficient.  I don't see anything in the available options to create a Primary Index on a table.

How can I create a Primary index on the table for column PayRecID?
Avatar of Abhimanyu Suri
Abhimanyu Suri
Flag of United States of America image

Indexes that are created as the result of creating PRIMARY KEY or UNIQUE constraints cannot be dropped by using DROP INDEX.
They are dropped using the ALTER TABLE DROP CONSTRAINT statement.

"I deleted the PrimaryKey index,, which was on the Indentity column (PayRecID) of the table" -- Did you drop index or constraint here ?

Please check the columns primary key constraint is on, it could be a composite non clustered index and you may have dropped some other clustered index that involves one of the columns of PK.

In case the table has FOREIGN KEY constraints, those constraints would have to be removed first.
Avatar of mlcktmguy

ASKER

I deleted the index while in SSMS.  In the object explorer I expanded the table, then expanded the indexes.

All the indexes including the Primary Key show there.  I right clicked on the Primary Key index and selected 'Delete' from the drop down.  Gone.

The picture is not of the table I removed the Primary Key From

User generated image
Turns out to be way easier than I thought.  In the same object explorer, righ click on the table and select 'Design'.

Click on the column to be the Primary Key

Then click on the Primary Key symbol in the header.  That's it.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Abhimanyu Suri
Abhimanyu Suri
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you.  I'm on a steep learning curve.