Clever Geek Handbook

Mysql: How to create a table with multiple primary keys?

I have a situation like this:

MySQL - a question for newbies: what are the PKs and FKs for these tables? (see table Salaries )

How do I 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 get an error if I try to execute the above code. Any ideas?

+2


source to share


6 answers


There can be only one primary key in a table. However, the primary key can have multiple columns, for example.



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


source


you can only create one primary key. If you want other fields to be unique then you need to create UNIQUE CONSTRAINT



+3


source


The other answers technically reflect your personal question, but you have a serious development flaw.

After reading your other question, it looks like it will be the middle table in a Many-Many join. If so, both of these fields will be foreign keys in this table and primary keys in the tables they refer to.

As foreign keys, you do not need to follow any special specification when creating them, other than to index them. Also, you don't want them to be unique in this table, they must account for duplicate values ​​in each, so you can join multiple elements on both sides of the MM join. You also don't want any of these fields to grow automatically in this table. They should do it in reference tables (employee / department)

+2


source 


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 autoincrement on both of them though

0


source


There can be only one primary key in a table , although it can be composite. Considering your previous post, I am assuming you are looking for multiple 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)
);
0


source


Your design is badly flawed as described in another question you linked to.

Dep_ID is probably not salary related, it belongs (as a primary key) in departments and as a foreign key for employees. If it relates to salary (because your employees can be paid from two departments at the same time, or can change departments, and you are storing the statistical salary data), it should not be part of the primary key.

The primary key in salary should be either one auto-incrementing salary column, or, if your project needs to store only one current salary record per employee, employee_id.

0


source






More articles:


All Articles