2

Making a primary key in a table in database is fine. Making a Composite Primary is also fine. But why cant I have 2 primary keys in a table? What kind of problems may occur if we have 2 primary keys.

Suppose I have a Students table. I don't want Roll No. and Names of each student to be unique. Then why can't I create 2 primary keys in a table? I don't see any logical problem in it now. But definitely I am missing a serious issue that's the reason it does not exist.

I am new in databases, so don't have much idea. It may also create a technical issue rather. Will be happy if someone can educate me on this.

Thanks.

Sandy
  • 11,332
  • 27
  • 76
  • 122
  • See: http://stackoverflow.com/questions/7126350/why-we-cant-have-more-than-one-primary-key/7127210#7127210 – nvogel Oct 23 '11 at 15:49

5 Answers5

2

You can create a UNIQUE constraint for both columns UNIQUE(roll,name).

yoprogramo
  • 1,306
  • 9
  • 14
2

The PK is unique by definition, cause it is used to identify a row from the others, for example, when a foreign key references that table, it is referencing the PK.

If you need another column to 'act' like a PK, give it the attributes unique and not null.

juankysmith
  • 11,839
  • 5
  • 37
  • 62
1

Well, this is simply by definition. There can not be two "primary" conditions, just like there can not be two "latest" versions.

Every table can contain more than one unique keys, but if you decide to have a primary key, this is just one of these unique keys, the "one" you deem the "most important", which identifies every record uniquely.

If you have a table and come to the conclusion that your primary key does not uniquely identify each record (also meaning that there can't be two records with the same values for the primary key), you have chosen the wrong primary key, as by definition, the fields of the primary key must uniquely define each record.

That, however, does not mean there can be no other combination of fields uniquely identifying the record! This is where a second feature kicks in: referential integrity.

You can "link" tables using their primary key. For example: If you have a Customer table and an Orders table, where the Customers table has a primary key on the customer number and the Orders table has a primary key on the order number and the customer number, that means:

  1. Every customer can be identified uniquely by his customer number
  2. Every order is uniquely identified by the order number and the customer number

You can then link the two tables on the customer number. The DB system then ensures several things, among which is the fact that you can not remove a customer who has orders in your database without first removing the orders. Otherwise, you would have orders without being able to find out the customer data, which would violate your database's referential integrity.

If you had two primary keys, the system would not know on which to ensure referential integrity, so you'd have to tell the system which key to use - which would make one of the primary keys more important, which would make it the "primary key" (!) of the primary keys.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • 1
    Referential Integrity constraints can reference any or all candidate keys of a table, not just one. Typically one key per table is used for that purpose but that doesn't have to be so. – nvogel Oct 21 '11 at 12:57
1

You can have multiple candidate keys in a table but by convention only one key per table is called "primary". That's just a convention though and it doesn't make any real difference to the function of the keys. A primary key is no different to any other candidate key. If you find it convenient to call more than one key "primary" then I suggest you do so. In my opinion (I'm not the only one) the idea of designating a "primary" key at all is essentially an outdated concept of very little importance in database design.

You might be interested to know that early papers on the relational database model (e.g. by E.F.Codd, the relational model's inventor) actually used the term "primary key" to describe all the keys of a relation and not just one. So there is a perfectly good precedent for multiple primary keys per table. The idea of designating exactly one primary key is more recent and probably came into common use through the popularity of ER modelling techniques.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

Create an unique index on the 2nd attribute (Names), it's almost the same as primary key with another name.

From Wikipedia (http://en.wikipedia.org/wiki/Unique_key):

A table can have at most one primary key, but more than one unique key. A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not. Thus, the values in unique key columns may or may not be NULL, and in fact such a column may contain at most one NULL fields. Another difference is that primary keys must be defined using another syntax.

palacsint
  • 28,416
  • 10
  • 82
  • 109
  • A unique constraint/index is not the same as a PK, uniques allow NULLs and you can only have one PK. – mu is too short Oct 21 '11 at 06:39
  • @mu, for the purpose of enforcing keys in SQL, a UNIQUE constraint *is* the same as a PRIMARY KEY. No key permits nulls. As you point out, the only difference is the pointless limitation that a PRIMARY KEY constraint can't be used more than once per table. – nvogel Oct 21 '11 at 12:49
  • @dportas: It depends on the DBMS. AFAIK MSSQL does not allow multiple NULLs but MySQL does: "For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL" http://dev.mysql.com/doc/refman/5.5/en/create-table.html – palacsint Oct 21 '11 at 13:14
  • @palacsint. For a key, all the columns concerned would have to have a NOT NULL constraint anyway. The use of a UNIQUE constraint doesn't change that, so the question of nulls doesn't arise. – nvogel Oct 21 '11 at 13:21