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.

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.