Are multiple Primary Keys in MySQL allowed?

Question

I haven't used SQL for ages and now I have to create a software with a small DB. I've decided to use MySQL with MySQL Workbench to help me design this database (by drawing a model with ER diagram).

When I've started adding relations, I found out that with each relation added to table, another PK is added to it. At first, I thought that it is how this GUI marks both Primary and Foreign Keys, but then I saw the output SQL code (query), e.g.:

CREATE TABLE IF NOT EXISTS `test`.`table` (
 `id` INT NOT NULL,
 ...
PRIMARY KEY (`id`, `ppe_id`, `type_id`, `code_id`),
...

So my question is - are those other fields/columns also Primary Keys? Up till now I thought that only a single PK is permitted per table.

Actually, those additional PKs gave me an error in the end, when I tried running SQL statements generated by Workbench on server, because I need one of those "key-fields" to allow NULL values. To solve that I removed a tick for PK checkbox on this nullable column. So additional (strongly related) question: should I remove all those additional PKs from all of my tables as well? Even if they can have NOT NULL constraint, their values won't be unique, because those are used mostly for one-to-many relations.

• DEarTh: stackoverflow.com/questions/217945/…
• joeljpa: Strongly recommend dba.stackexchange.com/questions/188995/…

Answer 1

Only one primary key per table is permitted. But a primary key can consist of any number of columns. Having many columns in a primary key however is often (not always) a bad choice.

Some notes about this...

• InnoDB tables are organized by primary key.
• As a consequence, all secondary indexes contain the primary key. Having a big primary key means having big indexes.
• Another consequence is that rows should be inserted in the order of the primary key (this happens automatically if you use an autoincrmeental id). Otherwise, InnoDB will have to do additional work to maintain the physical order of rows.
• Yet another consequence is that updating the primary key values is a slow operation that shouldn't happen normally.
• Lastly, if you don't keep in mind that the primary key is contained by all secondary indexes, with InnoDB it is easy to create indexes with duplicated columns.
• A query can use a whole index or a contiguous leftmost part. In your case, for example, a query that does not mention id cannot possibly take advantage of the primary key.

• ypercubeᵀᴹ: Updating a primary key is a slow operation? Why would it be slow to update a single row?
• Soul Reaver: Ok, seems that I have mistaken PK with a column (for me single column = single key, so multiple columns meant multiple keys). So, I'll answer my second question myself - yes, those automatic primary keys are to be removed, leaving just simple columns, with single primary key column per table. Thank you.
• Vérace: What about joining tables which naturally have a two column primary key?
• Federico Razzoli: @ypercubeTM because the rows are ordered by PK. BTW I wrote this only for completeness - do you know a real case when an application often updates PK values?
• Federico Razzoli: @Vérace this is a very good question. If the table is written often, I strongly suggest it has an id. Otherwise, a 2 columns PK is good.