Manage Learn to apply best practices and optimize your operations.

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:


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.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.