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.