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?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Abhimanyu SuriDatabase EngineerCommented:
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.
0
mlcktmguyAuthor Commented:
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

SQL Object Explorer
0
mlcktmguyAuthor Commented:
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.

Set Primary Key
1
Abhimanyu SuriDatabase EngineerCommented:
Hi mlcktmguy,

When you did " I right clicked on the Primary Key index and selected 'Delete' from the drop down.  Gone."

Actually, below statement gets executed in background

ALTER TABLE [dbo].[TABLE_NAME] DROP CONSTRAINT [PK_CONS_NAME]

which implicitly drops the index associated to it.

The right way to do is

ALTER TABLE [dbo].[TABLE_NAME] ADD  CONSTRAINT [PK_CONS_NAME] PRIMARY KEY CLUSTERED
(
      [COL1] ASC,
)

This command will implicitly create index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thank you.  I'm on a steep learning curve.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.