Take the 2-minute tour ×

Can i have multiple primary keys in a single table?

share|improve this question
add comment

9 Answers

You can have a Composite Primary Key which is a primary key made from two or more columns. For example:

CREATE TABLE userdata (
  userid integer,
  userdataid integer,
  info char(200),
  primary key (userid, userdataid)

Update: Here is a link with a more detailed description of composite primary keys.

share|improve this answer
+1 for the link, Adam. Definitely helped me out a lot. Thanks. –  Mike Sanchez Jan 12 '12 at 19:36
add comment

You can only have one primary key, but you can have multiple columns in your primary key.

You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values.

share|improve this answer
+1 for mentioning Unique Indexes –  Steve Goykovich Dec 18 '12 at 18:06
add comment

A table can have multiple candidate keys. Each candidate key is a column or set of columns that are UNIQUE, taken together, and also NOT NULL. Thus, specifying values for all the columns of any candidate key is enough to determine that there is one row that meets the criteria, or no rows at all.

Candidate keys are a fundamental concept in the relational data model.

It's common practice, if multiple keys are present in one table, to designate one of the candidate keys as the primary key. It's also common practice to cause any foreign keys to the table to reference the primary key, rather than any other candidate key.

I recommend these practices, but there is nothing in the relational model that requires selecting a primary key among the candidate keys.

share|improve this answer
Agreed. All keys are equal (none is 'primary') in the logical model. The choice of which key in the physical implementation gets the PRIMARY KEY designation is arbitray and vendor/product dependent. –  onedaywhen Oct 22 '08 at 7:49
I would say that it's database designer dependent. –  Walter Mitty Jun 20 '13 at 19:41
add comment

This is the answer for both the main question and for @Kalmi's question of

What would be the point of having multiple auto-generating columns?

This code below has a composite primary key. One of its columns is auto-incremented. This will work only in MyISAM. InnoDB will generate an error "ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key".

DROP TABLE IF EXISTS `test`.`animals`;
CREATE TABLE  `test`.`animals` (
  `grp` char(30) NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`grp`,`id`)

INSERT INTO animals (grp,name) VALUES

SELECT * FROM animals ORDER BY grp,id;

Which returns:

| grp    | id | name    |
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
share|improve this answer
add comment

As noted by the others it is possible to have multi-column primary keys. It should be noted however that if you have some functional dependencies that are not introduced by a key, you should consider normalizing your relation.


Person(id, name, email, street, zip_code, area)

There can be a functional dependency between id -> name,email, street, zip_code and area But often a zip_code is associated with a area and thus there is an internal functional dependecy between zip_code -> area.

Thus one may consider splitting it into another table:

Person(id, name, email, street, zip_code)
Area(zip_code, name)

So that it is consistent with the third normal form.

share|improve this answer
add comment

Some people use the term "primary key" to mean exactly an integer column that gets its values generated by some automatic mechanism. For example AUTO_INCREMENT in MySQL or IDENTITY in Microsoft SQL Server. Are you using primary key in this sense?

If so, the answer depends on the brand of database you're using. In MySQL, you can't do this, you get an error:

mysql> create table foo (
  id int primary key auto_increment, 
  id2 int auto_increment
ERROR 1075 (42000): Incorrect table definition; 

In some other brands of database, you are able to define more than one auto-generating column in a table.

share|improve this answer
What would be the point of having multiple auto-generating columns? –  Kalmi Aug 7 '09 at 16:34
I don't have a use case in mind, but if there ever were a need, some brands of database would support this and some would not. That's all I'm saying. –  Bill Karwin Aug 7 '09 at 17:28
add comment

Good technical answers were given in better way than I can do. I am only can add to this topic:

If you want something that not allowed/acceptable it is good reason to take step back.

  1. Understand the core of why it's not acceptable.
  2. Dig more in documentation/journal articles/web and etc.
  3. Analyze/review current design and point major flaws.
  4. Consider and test every step during new design.
  5. Always look forward and try to create adaptive solution.

Hope it will helps someone.

share|improve this answer
add comment

Yes, Its possible in SQL, but we can't set more than one primary keys in MsAccess. Then, I don't know about the other databases.

share|improve this answer
add comment

A primary key is the key that uniquely identifies a record and is used in all indexes. This is why you can't have more than one. It is also generally the key that is used in joining to child tables but this is not a requirement. The real purpose of a PK is to make sure that something allows you to uniquely identify a record so that data changes affect the correct record and so that indexes can be created.

However, you can put multiple fields in one primary key (a composite PK). This will make your joins slower (espcially if they are larger string type fields) and your indexes larger but it may remove the need to do joins in some of the child tables, so as far as performance and design, take it on a case by case basis. When you do this, each field itself is not unique, but the combination of them is. If one or more of the fields in a composite key should also be unique, then you need a unique index on it. It is likely though that if one field is unique, this is a better candidate for the PK.

Now at times, you have more than one candidate for the PK. In this case you choose one as the PK or use a surrogate key (I personally prefer surrogate keys for this instance). And (this is critical!) you add unique indexes to each of the candidate keys that were not chosen as the PK. If the data needs to be unique, it needs a unique index whether it is the PK or not. This is a data integrity issue. (Note this is also true anytime you use a surrogate key; people get into trouble with surrogate keys because they forget to create unique indexes on the candidate keys.)

There are occasionally times when you want more than one surrogate key (which are usually the PK if you have them). In this case what you want isn't more PK's, it is more fields with autogenerated keys. Most DBs don't allow this, but there are ways of getting around it. First consider if the second field could be calculated based on the first autogenerated key (Field1 * -1 for instance) or perhaps the need for a second autogenerated key really means you should create a related table. Related tables can be in a one-to-one relationship. You would enforce that by adding the PK from the parent table to the child table and then adding the new autogenerated field to the table and then whatever fields are appropriate for this table. Then choose one of the two keys as the PK and put a unique index on the other (the autogenerated field does not have to be a PK). And make sure to add the FK to the field that is in the parent table. In general if you have no additional fields for the child table, you need to examine why you think you need two autogenerated fields.

share|improve this answer
add comment

protected by Michael Myers Nov 25 '10 at 5:33
