How To Create Primary Key In SQL Server

Neerajprasadsharma
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 756 red flag
Rating: 5 out of 5  
 1 vote(s)

Learn what is the importance of primary key, why we should use it and how to create Alter and drop the primary key in SQL Server.

Introduction

Learn what is the importance of primary key, why we should use it and how to create Alter and drop the primary key in SQL Server.

Primary Key in SQL Server

Primary Key is a column or group of columns* which identify each row uniquely,  doesn`t accept null values and use to maintain referential integrity.
*primary key with more than one column called composite key.

Why use primary key:

Identify Record Uniquely: 
Enforcing Primary key on column(s) help user to avoid duplicate data in tuples (Rows). This helps identifying each data uniquely and it avoids duplication in the record. Example in school/collage each student has a roll number which identifies one student only.  

Enforce Referential Integrity:
It helps in creating a parent child relationship  using primary and foreign key.

Create Cluster Index:
Indexing helps in fast searching of row(s) in the table. Every book has an index page before its content to help reader directly jump to the desired chapter, that exactly index does for the table helps to find the record.  



Create Primary Key Syntax:
Create Table Table_Name (Column_Name DataType Primary Key ,Column_name2 DataType )   
If you want to name the constraint 
Create Table Table_Name (Column_Name DataType ConstraintName Primary Key ,Column_name2 DataType )    



 Let us do some practical work and create a table with primary key with constraint name.

Create Table [TableWithPK] (ID Int CONSTRAINT PK_ID PRIMARY KEY , name Varchar(10) not null ,Country Varchar(10) ) 



Now we have a table name [TableWithPK]  in the database contains primary key on the id column. If you want to check table`s property,  you can use the command: 
SP_HELP 'Table_name'
It shows every detail of the table, let`s check the property of your table. 




This  command shows everything about the table, like all columns in the table,  data types , primary key and its name etc.
If  you have already created a table and now want to make one or more columns as primary key, you can create it with the Alter table command. Let us have a look on below example:


Alter Table Add Primary Key Syntax:
Alter Table TableName Add Constraint PrimaryKeyName Primary Key (column) 

In the above example, we have already created a primary key on the ID column, and we cannot create two primary key on one table, so to continue the example we will first drop the primary key and then create again with the Alter table command.



Drop Primary Key In SQL Server Syntax:
Alter Table TableName Drop Constraint PrimaryKeyName 
  
Let us use the above syntax in the example:   

ALTER TABLE [TableWithPK] DROP CONSTRAINT PK_ID
ALTER TABLE [TableWithPK] add CONSTRAINT PK_ID primary key (ID)
 




Please not you can`t make the primary key to a column which is Nullable.

Page copy protected against web site content infringement by Copyscape

About the Author

Neerajprasadsharma
Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)