Benefits of Primary Keys in Database Tables

Benefits of Primary Key in Databases - Article on TechHowTos.com

A primary key is a special column (or set of combined columns) in a relational database table, that is used to uniquely identify each record. A database primary key cannot contain NULL values and each table can have only one primary key. A primary key can either be a single column, or a combined set of columns.

Benefits of Primary Key in a Database

The benefits of using primary keys in relational databases are many. The list below, summarizes the main benefits:

  • Database operations like searching for records are faster because an index is used, based on the primary key.
  • Using the primary key, you can easily identify and find unique rows in the database table.
  • They allow you to update/delete only specific records by uniquely identifying them.
  • The table’s data is sorted based on the primary key.
  • They help you avoid inserting duplicate records in a table.
  • The allow creating referential integrity constraints (i.e. use of foreign keys).

 

Benefits of Primary Key in Databases - Article on TechHowTos.com

 

Examples of Primary Keys in SQL Server Database

Below, you can find some simple examples of creating tables with primary keys in SQL Server databases.

 

SQL Server Table with Primary Key – No Auto-Increment

CREATE TABLE tblTest1 
  ( 
     id   INT PRIMARY KEY, 
     code VARCHAR(50) 
  ); 
GO

In the above example, the T-SQL code creates a SQL Server database table, along with setting the column “ID” as primary key. However, you will still need to include the value for the “ID” column in your INSERT statements, since auto-increment was not specified.

The second example, specifies auto-increment for the primary key.

 

SQL Server Table with Primary Key – With Auto-increment

CREATE TABLE tblTest2
  ( 
     id   INT PRIMARY KEY IDENTITY(1,1), 
     code VARCHAR(50) 
  ); 
GO

As you can see, in this second example, the T-SQL code creates a SQL Server database table, along with setting the column “ID” as primary key and also, it uses the IDENTITY property, in order for the primary key value, to be automatically set after each INSERT statement.

See more examples of the CREATE TABLE statement in SQL Server, in this MS Docs article.

 

Read Also

 

Featured Database Software Tool

SQLNetHub's Snippets Generator

 

SQLNetHub’s Snippets Generator 

Create and modify T-SQL code snippets for use in SQL Server Management Studio, fast, easy and efficiently.

Learn More

 

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: TechHowTos.com (https://www.techhowtos.com)

© 2018 TechHowTos.com

 

Share this!
About Artemakis Artemiou 30 Articles
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.