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.

Try to look into `INFORMATION_SCHEMA` to see if the primary key exists first, then drop the primary key if exists? Can't be done in single query. — Raptor, Aug 10 '16 at 06:42

score 6 · Answer 1 · answered Mar 17 '20 at 08:12

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.

score 2 · Answer 2 · edited May 23 '17 at 12:08

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:

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

score -1 · Answer 3 · answered Dec 29 '16 at 09:55

-1

I think the easy option might be this:

first go to : 'YourDatabase'>tables>your table name>keys>copy the constraints like 'PK__TableName__0001'

then run this:

Query:alter Table 'TableName' drop constraint PK__TableName__0001

answered Dec 29 '16 at 09:55

DisplayName

71
1
7

drop primary key if exists

3 Answers3