Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why two primary keys in a table is not allowed?

I want to understand the reasoning behind why two primary keys are not allowed in same table by grammar of MySql. I understand composite key and this is not related to that.

I could not understand why two separate primary key in same table is not allowed. But we can have one column as primary key and other column as unique and not null. Which practically acts as having two different primary keys as we can distinctly identify a row using any of these keys. Why the first one is not allowed but the later is?

I was trying to figure out the logic but stumbled upon Rule 2 of Codd's 12 rules which says

Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Does combination of table name, primary key value and column should be unique for a single datum value or can we have multiple combinations which guarantees access? Why or why not?

Edit: Since the question was identified as possible duplicate, I will explain below how it is different Other similar question was

Can I have multiple primary keys in a single table?

The ans is NO.

My question is why? What is the reasoning?

What is that Codd intends to say by rule no 2?

What problems would have occurred if multiple separate primary keys were allowed?

like image 898
Abhishek Jha Avatar asked Sep 03 '25 01:09

Abhishek Jha


1 Answers

You can have as many UNIQUE KEY constraints as your database system allows, and many relational purists now see it as a mistake to elevate one of these keys and anoint it as PRIMARY.

Logically, both unique key constraints and primary keys fulfil the same purposes - defining a subset of columns by which rows may be uniquely identified. They also should be eligible as targets for foreign key constraints.

Certain defaults (such as nullability) are automatically applied by primary key constraints but there's no reason you cannot apply the same constraints manually to columns included in unique key constraints.

like image 51
Damien_The_Unbeliever Avatar answered Sep 04 '25 16:09

Damien_The_Unbeliever