Q

How to choose the primary key columns in an Oracle table

Oracle expert Brian Peasland explains how to choose the primary key columns in an Oracle table and how to avoid violating Third Normal Form in this tip.

I want to create a table with four columns. Can I put all the four columns as a primary key? Is this good design?
To be honest, I cannot answer your question. Your primary key column(s) should be those columns which uniquely identify each row. Consider the following columns in a table:

NAME
SSN
EMPID

Now the EMPID and SSN columns are unique to the individual. Any of them can be the PK column. The NAME column is not necessarily unique, so by itself it cannot be the PK. However, adding a column to a column that can be a PK candidate is also a PK candidate. This means that (SSN,NAME) and (SSN,EMPID) can also be a PK as it is guaranteed to uniquely identify the row. But doing so is bad practice and violates Third Normal Form (3NF). The PK should uniquely identify the row and should also be a minimal key, meaning that you should not be able to remove any columns without losing the PK constraint. In the example of (SSN,NAME) for the PK, I can remove the NAME column and still have a PK constraint. So I would not use both columns in the PK constraint. There are a few cases where a PK column would be all columns in the table, but this is a rare situation.

As I do not know anything about your columns in this table, I cannot definitively answer your question.

This was first published in October 2009
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close