Ask the Expert

How to choose the primary key columns in an Oracle table

I want to create a table with four columns. Can I put all the four columns as a primary key? Is this good design?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top