CODEWITHSUNDEEP
mysqlphpmyadminmysql-error-1068
mohankarunasiri
mohankarunasiri

Reputation: 41

1068 multiple primary key defined in mysql

I export (with update) my existing MySQL backup and import it to another PC via PHPAdmin but it gives below error message. I need to just replace existing database on backup PC. Is it possible to do this?

#1068 - Multiple primary key defined

Upvotes: 4

Views: 30019

Answers (3)

Alex
Alex

Reputation: 6037

instead of drop database, empty it instead:

truncate tablename

(then add the pk again) When you do it this way don't have to recreate all tables and columns again.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108420

To do a wholesale replacement of an existing database...

One option is to drop the existing database and re-create it. But make sure you really want to do this, and that you are doing it on the MySQL instance you really want to do it on:

DROP DATABASE mydb ;
CREATE DATABASE mydb ;

Note that this may have an effect on granted privileges, you may need to re-grant privileges on the new database. (In the CREATE DATABASE statement, include a specification for the default characterset and collation, if those differ from the setting of the instance variables.)

SQLyog has a convenient "empty database" menu option, which drops all of the objects in the database without having to drop the database and re-create it. (This may have an effect on privileges granted to individual database objects.)

I'm not sure if phpmyadmin has an equivalent function. To roll your own, it's a matter of querying the information_schema tables to identify all tables, views, procedures, etc.

For example, to generate a list of the DROP TABLE statements, you could run a query something 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 the FOREIGN_KEY_CHECKS=0 for the session, or execute the drop statements in the appropriate order, etc. And repeat for the other object types: information_schema.routines, etc.

Upvotes: 0

Bitwise Creative
Bitwise Creative

Reputation: 4105

Yes, add drop table option in your export, then on import, your table will be dropped and recreated. This will avoid pk conflicts.

Upvotes: 4

Related Questions