Database Table and Primary Key – Part 2

ByHariharan Rajendran

Database Table and Primary Key – Part 2

In my previous article, I have explained about the table creation and primary keys.

In real time scenarios, a primary key field should generate values automatically whenever a new row added to the table.

If the primary key field is an integer then set up an identity

  1. Identity needs to be defined in (X, Y) format
  2. Define the value for X which is starting value and Y is an increment value.

CREATE TABLE Users

(Id int primary key identity (1,1),

Name varchar(50))

This table will generate a value for Id starting from 1 and increment by 1. The column values will be 1, 2, 3, .etc.

Primary key on unique identifier field,

CREATE TABLE Users

(Id uniqueidentifier default NEWID() primary key,

Name varchar(50))

This will generate random new GUID value for each row.

Choosing a right primary key column is very challenging.

We can create a primary key on any of the columns but we need to understand the business requirement.

Consider, if you are creating a primary key on Integer field. It has both advantages and disadvantages. Most of the time people will consider its advantages and start using integer as primary key column for all the tables.

Points to remember,

  1. Performance wise, this is the best option
  2. Applying and rebuilding the index is very easy
  3. If we consider a big environment where data storage is important. Then int will hold values up to Inserting records more than this limit will break the table.

Primary Key on unique identifier field,

Points to remember,

  1. It is very good idea to use unique identifier as a primary key column when data is less in the table
  2. It will occupy more disk space
  3. If data is huge then indexing and rebuilding are very hectic.

Primary key on varchar field, consider an emailid field

Point to remember,

  1. Sometimes we might think that our table should maintain a unique email address so considering this we may create aprimary key on email field. This is not advisable.
  2. Email address is a field which will change over the period of time.
  3. If considering this as a primary key field and involved on many to many relationships then if any changes to the email address, it will affect the whole database.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!