1

I have a database with many tables which can be joined by indexed keys (primary and foreign keys). Some tables are transaction tables which have an autoincremental primary key just to define a ID for each transaction.

To use this database and process the data (cleansing, adding new columns) I copy the database. The processes on the new database (updates, inserts) are quite complex. I index columns where it makes sense but I don't want to index columns which I don't need due to reason of performance.

Now, I come to the point: there are several huge transactions tables which have been defined from the original database as primary key but which are not used as key for joining. I would like to remove these primary keys (not the column but just: ALTER TABLE trans DROP PRIMARY KEY) to avoid too many indexed columns. Is there any reason not to remove index on a primary key?

If this question is dependent on RDBMS: I use MySQL/MariaDB.

2

In MySQL, if you have an auto_increment column, it MUST also be a key/index (either primary or a unique or a non-unique one, so any index really) where auto increment column is the first in the index/key definition.

For any InnoDB tables that do not have a PRIMARY or UNIQUE NOT NULL key defined, MySQL will implicitly create (a 6-byte) internal key under the hood and use it as the clustered index.

This clustered key (the PRIMARY or the first UNIQUE NOT NULL or the 6-byte internal) is always included in ALL secondary indexes for InnoDB tables in MySQL.

In your situation, dropping the PRIMARY key will only result in MySQL creating an implicit one anyway (unless you define another PRIMARY or UNIQUE NOT NULL key explicitly). This also applies only to InnoDB tables.

  • +1 for pointing out InnoDB @ypercubeᵀᴹ – thatsaru May 24 '17 at 15:08
  • Thanks for yours explanations, very helpful. For my understanding: 1) If the PK is more then 6-bytes (e.g. bigint or CHAR(7)) than it makes (probably) sense to use the implicitly created key otherwise hold the PK to avoid creating implicitly key? 2) In case of ARIA and MyISAM I can drop primary since there won't be created implicitly any key? – giordano May 24 '17 at 15:48
  • 2
    For InnoDB, I would keep the existing PK (especially if it's int, bigint or small char/varchar). Only if it was some long char, say varchar(50) I'd consider replacing it. And only if there are many secondary indexes. – ypercubeᵀᴹ May 24 '17 at 15:53
  • @ypercube I got the idea. If the useless primary key needs lot of resources replace it with the internal (implicitly) or a smaller one. Thanks for help. – giordano May 25 '17 at 8:40
2

Any table should really have a primary key, a value that uniquely identifies each row, especially if there are foreign keys referring to the table (otherwise there is nothing for the FKs to refer to). If the existing FKs don't refer to the current PK but instead refers to other not-nullable column(s) that is/are subject to a unique index, then column (or group of columns in the case of a composite key) should be the primary key. An extra caveat that might have driven the surrogate key being added is that it is usually recommended that the PK be immutable, so the extra incrementing ID column may have been added because the other candidate keys are not immutable.

As the PK essentially becomes part of other indexes the other reason a separate integer column was added is index size: using a wider PK would enlarge the size of all other indexes.

  • There are no FKs in other tables referring to the PKs of the transaction tables. The PKs of the transaction tables are just identifiers (that's why I like to have it) but have no other function. – giordano May 24 '17 at 15:32
  • Thanks. I just wonder what means "becomes part of other indexes" and "wider PK would enlarge the size of all other indexes." – giordano May 24 '17 at 16:20
  • 1
    @giordano, in MySQL a primary key is automatically added (internally) to all other keys/indexes. That is the reason it is recommended to have the primary key as "small" as possible. – thatsaru May 25 '17 at 7:52

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.