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

Question

I have this situation:

http://stackoverflow.com/questions/1590033/mysql-newbie-question-which-are-the-pk-and-the-fk-for-this-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?

Answer 1

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

Answer 2

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)
);

Answer 3

create table salaries
( dep_id smallint auto_increment,
  an_id smallint auto_increment,
  bla varchar(20),
  PRIMARY_KEY (dep_id, an_id)
);

not sure if you can use auto-increment on both of them though

Answer 4

A table can only have one primary key, altough it could be a composite one. Given your earlier post, I'm guessing you're looking for several foreign keys, not two primary ones.

create table salaries
(
  dep_id SMALLINT, 
  emp_id SMALLINT, 
  bla varchar(20),
  INDEX (dep_id, emp_id),
  FOREIGN KEY (dep_id) REFERENCES Department(dep_id),
  FOREIGN KEY (emp_id) REFERENCES Employees(emp_id)
);

Answer 5

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)

Answer 6

Your design is significantly flawed, as described in the other question that you linked to.

Dep_ID probably does not belong in salaries, it belongs (as a primary key) in departments and as a foreign key in employees. If it does belong in salaries (because your employees can be simultaneously paid from two departments, or can change departments and you maintain historical salary data) it shouldn't be part of the primary key.

The primary key in salaries should be either a single auto-incremented salary_id column or, if your design is to store only a single, current salary entry per employee, the employee_id.