1068 multiple primary key defined in mysql

Question

I export (with an update) my existing My SQL backup and import it to another computer through PHPAdmin, but the error message below is shown. I just need to replace the existing database on the backup PC. Can this be done?

"# 1068 - Multiple primary key defined

Thanks Mohan K

Answer 1

Yes, add the drop table option to your export, then upon import, your table will be deleted and recreated. This will avoid pk conflicts.

Answer 2

Make a wholesale replacement for an existing database ...

One option is to delete the existing database and recreate it. But make sure that you really want to do this, and that you are doing this on the MySQL instance on which you really want to do this:

 DROP DATABASE mydb ; CREATE DATABASE mydb ; 

Please note that this may affect the privileges granted, you may need to re-grant privileges in the new database. (In the CREATE DATABASE statement, specify the default character set and sort specification if they differ from the settings for instance variables.)

SQLyog has a convenient “empty database” menu option that deletes all objects in the database without dropping the database or creating it. (This may affect privileges granted to individual database objects.)

I am not sure if phpmyadmin has an equivalent function. To overturn your own, you need to query information_schema tables to identify all tables, views, procedures, etc.

For example, to generate a list of DROP TABLE statements, you can run a query like this:

  SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'` ;') AS stmt FROM information_schema.tables t WHERE t.table_schema = 'foo' ; 

If the tables have foreign keys, you can either set FOREIGN_KEY_CHECKS=0 for the session, or follow the reset instructions in the appropriate order, etc. And repeat for other types of objects: information_schema.routines , etc.

Answer 3

instead of a database flush, instead:

 truncate tablename 

(then add pk again) When you do this, you do not need to recreate all the tables and columns again.