Mysql: How do you create a table with multiple primary keys?

Question

I have this situation:

MySQL - Newbie question: Which are the PK and the FK for these tables? (take a look at the table Salaries)

How do you create a table with multiple primary keys?

create table salaries
(
  dep_id smallint auto_increment primary key, 
  emp_id smallint auto_increment primary key, 
  bla varchar(20)
);

I receive an error if I try the code above. Any ideas?

Solution 1

A table can only have one primary key. However, the primary key can consist of multiple columns, e.g.

CREATE TABLE salaries (
    dep_id SMALLINT UNSIGNED NOT NULL,
    an_id SMALLINT UNSIGNED NOT NULL,
    bla VARCHAR(20),
    PRIMARY KEY (dep_id, an_id)
);

Solution 2

you can only create ONE primary key. If you want the other fields to be unique, you have to create UNIQUE CONSTRAINT

Solution 3

The other answers technically address your literal question, but you have a serious design flaw in progress here.

After reading your other question, it looks like this is intended to be the middle table in a Many-Many join. If this is the case, both of those fields would be foreign keys in this table and primary keys in the tables they link to.

As foreign keys you don't need to do any special specification when creating them other than being sure to index them. Also, you don't want them to be unique in this table, they have to accommodate duplicate values in each so you can join multiple items on both sides of the M-M join. Nor do you want either of these fields to auto-increment in this table. They should do that in the referenced tables (employee/department)

Comments

[cc.]

Primary key is by default auto_increment?

[OMG Ponies]

@cc: You don't want it to be auto increment in this case. The answer needs to include the foreign key setup to be 100% correct.

[dburges]

MOst databases do not allow autoincrement on more than one key. It is rare to want to autoincrement on a composite key anyway as they are often a combination of Fks from some other table or string type data.