Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

A Primary Key is a special column in the relational database table that is dedicated to identifying records within a table uniquely. In other words, a single column or a set of columns that uniquely identifies each row in the table is called the Primary key or generally abbreviated as PK in the SQL.

Before you add a primary key to the database, you should add primary key constraints first. In case, there are multiple columns used as the primary key then it is named as the composite primary key but make sure that you are using a few columns only.

The Primary Key is good in both performance and storage. The more columns are you using as the primary key, more storage you needed generally. When we are talking about the performance, then less data generally leads to better performance standards.

What are the Features?

  • The Primary Key in SQL works on entity integrity of one table and Foreign key in SQL works on the referential integrity of one table. You must be wondering what is a Foreign key in SQL, for more detail you should check our clog on foreign key and the related terminologies.
  • The primary key always has the unique data.
  • The length of a primary key cannot be exceeded more than 900 Bytes.
  • There could not be NULL values for a primary key.
  • You could not add duplicate values to the primary key.
  • One table could have only one primary key constraint.

SQL Server Curriculum

What is a Primary Key in SQL?

A primary key is defined as the single column or a set of columns that uniquely identifies each row in the table. As soon as one primary key constraint is defined for the table, the database engine automatically creates a unique index for the primary key column. The biggest advantage of defining primary key is that we get faster access. This is not possible adding a primary key constraint if it contains more than 32 columns.

The primary key is taken as the critical component of an efficient relational database system. In the absence of the primary key that is closely related to the Foreign key in SQL, the relational database system would not work as usual. Almost everyone deals with the primary key in their daily life unknowingly. For example, students are given unique ID no in Schools and college, employees are given a unique ID in the offices, all US citizens have uniquely identifiable social security numbers as given by the government.

Read: SQL Having Clause Functions

If we will take the example of a Bank then branch code or bank name is same for all users but they are given unique account number for identification. In order to identify all customers uniquely, the bank makes sure two of the users should not have the same account number. In case, values are the same then they should be eliminated quickly. As soon as some column is defined as allows secure handling of data and faster method of data access too.

For example, if you are asked to tell your surname then multiple results will be displayed but with the help of primary key only one result is returned and it makes data handling much easier than your expectations.

How to add a primary key in SQL?

Primary keys are usually specified at the time of new table creation or at the time when changing the structure of the existing table using the Alter Table statement. Here, in the example below, we have given the general syntax for the primary key constraint using ‘Create Table’ and ‘Alter Table’ both. Primary Key in SQLPrimary Key in SQL A constraint is the combination of the NOT NULL and the UNIQUE constraint. It ensures that the particular column is the combination of two or more columns in a table and have a unique identity too that helps in identifying the particular record within a table more quickly and easily. SQL Server quiz

Syntax to add a primary key for one column using Create table -

Primary Key in SQL

Syntax to add a primary key for one column on Alter table -

Primary Key in SQL

Syntax to add a primary key for multiple columns -

Primary Key in SQL

Read: Top 50 Informatica Interview Questions and Answers

Syntax to add a primary key for multiple columns on Alter table -

Primary Key in SQL

How to set the primary keys in SQL?

Setting the primary keys in SQL is one of the important factors in database design because the choice of a primary key could have the significant impact on the performance, and usability of the entire database. Here, we have given the best practices to follows when setting the primary key in SQL to make sure that database lookups are speedy and reliable.

Best choice to set the primary key

Best choice to set the primary key

  • Keep it Short – try to make the primary key as small as possible. It is generally needed for the database lookups and the comparisons. When it will be kept short then easy to process as compared to the long Primary Key.
  • Use a number – Put the primary key in numeric whenever possible. Numeric data is easy to process and remember as compared to the character data.
  • Never change – Once the primary key has been assigned, it should never change. In a few cases, the primary key is used as the pointer as well to refer the other tables in a relational database system. This dependency is the reason why the primary key should never change in the SQL.
  • Make it Simple – You should not use any special characters, or symbols when defining the primary keys. Also, you should not use passport number, social security number or the employee Id because they could change for real-world situations.
  • Surrogate Keys – One of the excellent techniques for defining the primary key is using surrogate keys. These are system generated keys whose values are never defined NULL and unique for each entry.
  • Non-identifying values – One of the most common mistakes made by database designers to put intelligence when defining the primary key. Mostly, one descriptive primary key could become obsolete later.
  • Data Types – When it comes to data types, you could have a plenty of choices. But you are strongly recommended using numeric data types that can process faster as compared to the character data types. Further, fixed-length data types are easy to process when compared to the variable-length data types.

Based on the conditions as specified earlier, you should match the criteria and define the primary key accordingly. The candidate that satisfies the maximum number of conditions is taken as the primary key of a table. If you don’t find any suitable candidate then consider the surrogate key as the primary key.

Poor choices to set the Primary Key

A few database developers may consider these options the obvious choices for the primary key but we strongly recommend avoiding these values or they are taken as the poor choice instead, by experienced database developers.

Poor choices to set the Primary Key

Read: What is NoSQL? NoSQL Tutorial Guide for Beginner
  • ZIP CODES – you should never use the ZIP Code as the primary keys. When looking up the cities, zip code is a common choice. Based on a detailed investigation, you will quickly realize that zip codes are common in different cities and creating confusion too. For example, the New Jersey cities of Neptune, Neptune City, Tinton Falls, and Wall Township all share the 07753 ZIP code.
  • SSNs (Social Security Numbers) – SSNs don’t make good primary keys due to several reasons. Most of the people don’t want to make their SSNs public or clearly visible to others. At the same time, there are a few people who don’t have any social security numbers. Also, SSNs are assigned on the death of an individual. An individual could have more than one social security number in case of the identity theft.
  • Email Address – This is another poor choice for the primary key. Although they are unique, it may change over time especially when it is an official ID because people keep changing their jobs after a few years. Most importantly, even in the internet age, not everyone has a unique email ID.

  free SQL Server demo

How to remove the primary key in SQL?

The process of removing a primary key is pretty straightforward. If you can create the primary key then there is provision to delete it as well based on the requirements. To remove the primary key in SQL, drop keyword is used. Here, is the syntax to remove the primary key in the SQL- Primary Key in SQL Primary Key in SQL

As you can see that deleting a primary key in SQL is quite easier but it will remove all indexes too associated with the primary key. If the index is clustered, it means that we have a huge heap on our hands and it can affect the performance of non-clustered indexed that were pointing to the clustered indexes. Further, it will hit the performance as well based on the complexity of queries and the amount of data. So, you have to be extra careful when dropping the primary keys. You should not drop the primary key on production servers during the peak hours. With this blog, you will get a deep idea of the primary key and how to create, set, or remove the primary key in SQL. A depth understanding of the concept always helps you in a successful implementation and wondering database management too.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews