Does it makes sense to index a primary key if it is not used?

Question

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.

Answer 1

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.

Answer 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.