0

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.

2

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.
  • 1
    Updating a primary key is a slow operation? Why would it be slow to update a single row? – ypercubeᵀᴹ Apr 19 '18 at 9:49
  • 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. – Soul Reaver Apr 19 '18 at 9:55
  • 2
    What about joining tables which naturally have a two column primary key? – Vérace Apr 19 '18 at 10:31
  • @ypercubeᵀᴹ 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 Apr 19 '18 at 11:33
  • @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. – Federico Razzoli Apr 19 '18 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.