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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: