The Database Design Resource Center

Candidate keys | Primary keys | Referential integrity

Primary and foreign keys - How to find your rows

This section deals with primary and foreign keys; candidate keys and secondary keys. It will contain excerpts from the eBook on Primary Keys, which is to be released later.

I will be presenting a few keynotes here taken from the eBook.

Finding and defining unique identifiers for your entities is an important task. These identifiers will eventually become primary keys in your tables.

The primary key definition of one table will be propagated to related tables (underlying tables) as foreign keys in each table.

Tables may have several candidate keys: Columns that are considered to be used as primary keys.

And, you may want to define secondary keys, unique or non-unique, for performance reasons: After the system is built, you may discover bottlenecks that can be removed by adding indexes on columns that are not a part of the primary or foreign keys.

Some analysts and developers claim that the only right thing to do is to assign just a computer generated number, without any meaning, as primary keys. This is what is called surrogate, or synthetic primary keys.

Then others again tell you that you should always have natural keys, based on attributes that evolve during the analysis phase, as primary keys.

What shall one do?

  • This article relates to Candidate Keys: column(s) that contain a term which may be an actual primary key for the table. Due to the length of this article, I recommend that you make a printed version of it. Read the full article on Candidate Keys
  • Referential integrity is an extremely important concept in database design. But what does the term really mean? This article explores Referential Integrity

Return to Database Design Home

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.