drop primary key if exists

Question

I need a query that could drop primary key only if it exists.

ALTER TABLE tablename DROP PRIMARY KEY;

This will return error if it does not exists, but my requirement is to run a query in different databases.

Answer 1

In MariaDB 10.2.16 i was able to solve this problem with:

ALTER TABLE tablename DROP INDEX IF EXISTS `PRIMARY`;

This should work with any table since the primary keys in MySQL are always called PRIMARY as stated in MySQL doc:

The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

Answer 2

I would recommend using this:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.',TABLE_NAME, 
' DROP PRIMARY KEY; ANALYZE TABLE ', TABLE_SCHEMA, '.',TABLE_NAME, ';') 
FROM information_schema.COLUMNS 
WHERE CONCAT(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME) IN  
    (SELECT CONCAT(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE INDEX_NAME = 'PRIMARY' -- *Required* to get only the primary keys from the statistics table.
    -- *Optional*
    AND TABLE_SCHEMA = 'clients_database');

• Run this to generate your required SQL.
• Copy your results, then run them as working queries.
• ANALYZE TABLE is optional as well as the WHERE clause.
• You can remove ANALYZE TABLE ', TABLE_SCHEMA, '.',TABLE_NAME, ';' if desired from the query below.

I exploit the information_schema when researching and utilizing standardization techniques.

Just about everything you would ever need or want to know about your tables and columns lives in some System table in either (if applicable)

Database / Table_schema:

• information_schema

• performance_schema

• mysql

Note: From doc's

Internal schemas, such as "performance_schema", "information"schema", "sys", and "mysql", are hidden by default. Toggle the Show Metadata and Internal Schemas preference to list them in the object browser. Schemas beginning with a "." are also controlled by this setting.

NOTE: Here's something similar that has been created.

Hope this helps!

Cheers,

Jay