Problem solve Get help with specific problems with your technologies, process and projects.

Why not two primary keys for a single table?

Why don't we have two primary keys for a single table?

Why don't we have two primary keys for a single table?

This goes back to the 1970s when Dr. Codd was creating the relational database model. As you can imagine, it is possible for a table to have more than one potential primary key. For instance, in a table of employees, the employee ID field and the employee's SSN can all be potential primary keys. In relational database theory, we refer to these as "candidate keys." While a table can have multiple candidate keys, only one of them can be the primary one. This is defined as the "primary key."

When designing the relational schema, we attempt to break down entities into their smallest components, where each entity can have multiple attributes. The process of breaking down entities is called "normalization." Normalization is important as a properly normalized schema can stop data anomolies from occurring. Most database designs strive for something called Third Normal Form (3NF). In 3NF, all attributes of the entity are said to be implied by the key, the whole key and nothing but the key. For instance, in a table of employees, the employee's name can be directly derived from the SSN. And the employee's department number can be derived from this key. However, the department name is derived from the department number. Therefore, a 3NF table of employees cannot have the department name in that table. When working with normalization, you have to choose one key to work with. Even though you may have multiple candidate keys, you can only use one of them . . . the primary key.

Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.