Can I have multiple primary keys in a single table?

Question

Can i have multiple primary keys in a single table?

Answer 1

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.

Answer 2

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.

Answer 3

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; 
there can be only one auto column and it must be defined as a key

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

Answer 4

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.

Answer 5

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.

CREATE TABLE CHAPTER ( BOOK_ISBN VARCHAR(50) NOT NULL, IDX INT NOT NULL, TITLE VARCHAR(100) NOT NULL, NUM_OF_PAGES INT, PRIMARY KEY (BOOK_ISBN, IDX) );

Thanks, Anand.

Answer 6

How then, with a composite primary key, does one create a reference to this group of fields in the composite key as a foreign key in another table? Eg. if my parent table is (in SQL Server):

CREATE TABLE parent ( WBS char(9), Scenario char(20), ... CONSTRAINT [PK_parent] PRIMARY KEY CLUSTERED ( WBS, Scenario ))

how do I then create the FK reference in a child table with both WBS and Scenario as FKs?