How to choose the primary key columns in an Oracle table

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database design and architecture How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table Can I install an Oracle client on Windows 7? How to use the Oracle Database Upgrade Assistant (DBUA) Can I specify Oracle column order in my database table? Can I have a single Oracle 11g RAC instance across multiple databases? How to use the Oracle export utility to duplicate database structure Understanding the data archiving definition How to plan Oracle Grid Control Repository maintenance What Linux flavor should I use for my Linux proof of concept in Oracle? Traditional table vs. Index Organized Table (IOT) in Oracle Oracle database design and architecture How to determine your SQL database through needs analysis Breaking down the contenders in the SQL database market The MySQL open source database in the enterprise Diving deeper into the SQL database features What managers should consider when starting a database scaling project How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table Can I install an Oracle client on Windows 7? How to use the Oracle Database Upgrade Assistant (DBUA) Can I specify Oracle column order in my database table? Can I have a single Oracle 11g RAC instance across multiple databases?

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary E. F. Codd  (SearchOracle.com) extent  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) multidimensional database  (SearchOracle.com) object-oriented database management system  (SearchOracle.com) quad tree  (SearchOracle.com) relational online analytical processing  (SearchOracle.com) row  (SearchOracle.com) splay tree  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.