Benefits of Primary Keys in Database Tables - Article on essentialDevTips.com

Benefits of Primary Keys in Database Tables

by

Hi friends! This article, discusses about the benefits of primary keys in database tables.

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).

 

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.

 

Recommended course: “Essential SQL Server Development Tips for SQL Developers”

Make sure that you check our online course on Udemy, titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime access, downloadable resources, course completion certificate and more!)

Enroll to the Course

 

Recommended Online Courses:

 

Read Also:

 

Reference: {essentialDevTips.com} (https://www.essentialdevtips.com/)

© essentialDevTips.com

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

Loading...

Post Views: 1,593