Remove Primary Key in MySQL

Question

I have the following table schema which maps user customers to permissions on a live MySQL database   mysql gt  describe user customer permission   ------------------ --------- ------ ----- --------- ----------------    Field              Type      Null   Key   Default   Extra             ------------------ --------- ------ ----- --------- ----------------    id                 int 11    NO     PRI   NULL      auto increment     user customer id   int 11    NO     PRI   NULL                         permission id      int 11    NO     PRI   NULL                        ------------------ --------- ------ ----- --------- ----------------  3 rows in set  0 00 sec    I would like to remove the primary keys for user customer id and permission id and retain the primary key for id   When I run the command   alter table user customer permission drop primary key    I get the following error   ERROR 1075  42000   Incorrect table definition  there can be only one auto column and it must be defined as a key   How can I drop a column s primary key

User · Answer

I believe Quassnoi has answered your direct question  Just a side note  Maybe this is just some awkward wording on your part  but you seem to be under the impression that you have three primary keys  one on each field  This is not the case  By definition  you can only have one primary key  What you have here is a primary key that is a composite of three fields  Thus  you cannot  drop the primary key on a column   You can drop the primary key  or not drop the primary key  If you want a primary key that only includes one column  you can drop the existing primary key on 3 columns and create a new primary key on 1 column

User · Answer

Find the table in SQL manager right click it and select design  then right click the little key icon and select remove primary key

User · Answer

Without an index  maintaining an autoincrement column becomes too expensive  that s why MySQL requires an autoincrement column to be a leftmost part of an index   You should remove the autoincrement property before dropping the key   ALTER TABLE user customer permission MODIFY id INT NOT NULL  ALTER TABLE user customer permission DROP PRIMARY KEY    Note that you have a composite PRIMARY KEY which covers all three columns and id is not guaranteed to be unique   If it happens to be unique  you can make it to be a PRIMARY KEY and AUTO INCREMENT again   ALTER TABLE user customer permission MODIFY id INT NOT NULL PRIMARY KEY AUTO INCREMENT

User · Answer

One Line   ALTER TABLE   user customer permission  DROP PRIMARY KEY   ADD PRIMARY KEY     id      You will also not lose the auto-increment and have to re-add it which could have side-effects

User · Answer

First modify the column to remove the auto increment field like this  alter table user customer permission modify column id int   Next  drop the primary key  alter table user customer permission drop primary key

User · Answer

if you restore the primary key  you sure may revert it back to AUTO INCREMENT   There should be no question of whether or not it is desirable to  restore the PK property  and  restore the autoincrement property  of the ID column   Given that it WAS an autoincrement in the prior definition of the table  it is quite likely that there exists some program that inserts into this table without providing an ID value  because the ID column is autoincrement anyway    Any such program s operation will break by not restoring the autoincrement property

User · Answer

First backup the database  Then drop any foreign key associated with the table  truncate the foreign key table Truncate the current table  Remove the required primary keys  Use sqlyog or workbench or heidisql or dbeaver or phpmyadmin

User · Answer

In case you have composite primary key  do like this- ALTER TABLE table name DROP PRIMARY KEY ADD PRIMARY KEY  col name1  col name2

User · Answer

ALTER TABLE  table name  ADD PRIMARY KEY   column name

User · Answer

I had same problem and beside some values inside my table  Although I changed my Primary Key with   ALTER TABLEuser customer permissionDROP PRIMARY KEY   ADD PRIMARY KEY  id     problem continued on my server  I created new field inside the table I transfered the values into new field and deleted old one  problem solved

User · Answer

To add primary key in the column   ALTER TABLE table name ADD PRIMARY KEY  column name     To remove primary key from the table   ALTER TABLE table name DROP PRIMARY KEY

User · Answer

ALTER TABLE  user customer permission  MODIFY  id  INT  ALTER TABLE  user customer permission  DROP PRIMARY KEY

[mysql] Remove Primary Key in MySQL

Examples related to mysql

Examples related to sql

Examples related to database-design

Examples related to primary-key

Examples related to mysql-error-1075