Can a primary key contain more than one columns? [duplicate]

This question already has answers here: Closed 11 years ago.

Possible开发者_C百科 Duplicate:

is this possible to made two primary key in one table

Is it possible to define a primary key on more than one columne in MySQL?

How is this indicated in a create table statement?

---

Yes, you absolutely can.

Here's a link:

http://sqlzoo.net/howto/source/z.dir/tip241027/mysql

And here is the basic syntax:

CREATE TABLE myTable(
myCol1 INTEGER  NOT NULL,
myCol2 CHAR(10) NOT NULL,
myCol3 INTEGER NOT NULL,
myCol4 CHAR(1),
PRIMARY KEY (myCol1 , myCol2 , myCol3 )
)

---

You can use:

CREATE TABLE tableName (
firstName varchar(10) NOT NULL,
surname varchar(20) NOT NULL,
primary key(firstName,surname)
);

I personally use this structure when I use a lookup table to connect many-to-many fields:

CREATE TABLE personPhoneNumbersLookup (
personID int(3) NOT NULL,
phoneNumberID int(4) NOT NULL,
primary key(personID,phoneNumberID)
);

To ensure that I only connect one particular person to one particular phone number once only. Though, obviously, the same personID can be used for other phone numbers, and multiple phone numbers can be connected to the same person.

---

Yes, it can
CREATE TABLE table1(col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY(col1,col2));

UPDATE
It's possible, but I wouldn't recommend to overuse composite primary keys for mysql INNODB tables. For INNODB engine Primary Key is also a clustered index which defines physical location of the row data. Frequent changes of any columns which are part of PK will cause external fragmentation, and ,as a result, worse performance. Unique key on 2 columns will work much better.
Surely, composite PK is a good choice when you implement many-to-many relationship

---

As others have stated... yes you can... However, in many systems the tables will have "surrogate" auto-increment keys which would be your "PRIMARY" and used in most cases for your joins... this to prevent duplication of such other data as samples like MyCol1, MyCol2, MyCol3 and FirstName, SurName in other tables.

In addition, you can have a "candidate" key which is a UNIQUE index for the table that is IN ADDITION to your primary, such as duplicate entry prevention, or by "lookup" purposes. Doing a lookup by a person by name is one thing. Then, when you have their "Surrogate" ID, THAT is the key that would be used elsewhere in the system... Ex: How many times would you find a "Bob Smith" with completely unrelated entries... But ID #3892 "Bob Smith" at 123 Anyplace is different than ID#28928 "Bob Smith" at 983 Wrong Street.