Ask the Expert

Why only one primary key?

Why is there only one primary key allowed in a table? Why not more than one? Even then you can have multiple unique and not null constraints defined on any number of columns.

    Requires Free Membership to View

The answer lies in relational database theory, not Oracle. In relational database theory, you are allowed to have multiple "candidate keys." For instance, assume that a table of employees, EMP, holds the employee id, EMPID, and the employee's social security number, SSN. Since the EMPID and SSN columns are unique to each employee, and since each employee must be assigned values for these two columns, both columns are candidates for the primary key. In most cases, your table will only have one candidate key. But it is possible to have multiple candidate keys as I have illustrated above. Relational database theory says that when you have more than one candidate key, you must choose one to be the primary key. If you only have one candidate key, then it is, by default, your primary key.

Oracle will throw an error if you try to declare a second primary key on a table. If your table has more than one candidate key, you can still enforce the business rules by declaring one as the PRIMARY KEY and the other candidate key is constrained to be UNIQUE and NOT NULL.

This was first published in September 2006

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: