• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

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?
0
mlcktmguy
Asked:
mlcktmguy
  • 3
  • 2
1 Solution
 
Abhimanyu SuriSr Database 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 SuriSr Database 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
 
mlcktmguyAuthor Commented:
Thank you.  I'm on a steep learning curve.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now