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.
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