How to safely remove a primary key?

💻 Development
1

Hello,

TL;DR

I am developing for an app for NC. Here we recently stumbled upon the problem that we need to remove a primary key from a table. Short question: How can we safely undo this in a migration?

Example

Lets say we have one migration that has something like

$table = $schema->getTable('...');
$table->setPrimaryKey(['id']);

In a later migration, the key must be removed as the table structure has changed. So I would consider something like

$table = $schema->getTable('...');
if($table->hasPrimaryKey()) {
  $table->dropPrimaryKey();
}

This seems to work with SQLite and MySQL/MariaDB as far I can tell. The problem is that with PostgreSQL this is not working. You get a message like

SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop index oc_cookbook_names_pkey because constraint oc_cookbook_names_pkey on table oc_cookbook_names requires it
HINT:  You can drop constraint oc_cookbook_names_pkey on table oc_cookbook_names instead.

Question

I suspect changing of (unique) indices is done from time to time in other projects (be it the NC core or other apps). How can this be achieved safely without loss of generality?

With the term without loss of generality, I am thinking towards the different database systems possible as well as different versions of doctrine/DBAL.

Solution I have considered

I suspect, I can do something with DBAL to

  1. Check if there is a constraint _pkey
  2. Remove the constraint if so
  3. Eventually drop the index.

However, I did not find a way to get hold on the constraints using DBAL. Any help here?