How to remove primary keys in phpmyadmin

Question

I have the table shown, which shows AI with the little key icon next to the name (which I assume means it's a primary key) but the right side has primary key grayed out. Additionally for some reason I also have 4 other primary keys defined with no way to remove them (clicking the icon doesn't do anything). Is there any way to remove the keys? Or figure out why they were even set in the first place? I never set any manually myself.

You probably actually have one primary key covering multiple columns. Please post the results of `SHOW CREATE TABLE whatever_you_named_your_table`. — elixenide, Nov 16 '15 at 06:23
That doesn't seem to be doing anything. All it says is: Your SQL query has been executed successfully. — Robbie, Nov 16 '15 at 06:30
Look below that, probably near the middle of your screen. You should see results in two columns: `Table` and `Create Table`. Post the text under `Create Table`. — elixenide, Nov 16 '15 at 06:34
CREATE TABLE `mytable` ( `AI` int(11) NOT NULL AUTO_INCREMENT, `DateTaken` date NOT NULL, `DateCompleted` date NOT NULL, `Name` text NOT NULL, `Phone` text NOT NULL, `Address` text NOT NULL, `RealAddress` text NOT NULL, `City` text NOT NULL, `Appliance` text NOT NULL, `Model` text NOT NULL, `Make` text NOT NULL, `Lat` decimal(50,30) NOT NULL, `Lng` decimal(50,30) NOT NULL, `Problem` text NOT NULL, `Corrective` text NOT NULL, `Status` text NOT NULL, PRIMARY KEY (`AI`) ) ENGINE=InnoDB AUTO_INCREMENT=307 DEFAULT CHARSET=latin1 — Robbie, Nov 16 '15 at 06:40
Those little icons are just buttons you can click to add keys. You don't have multiple primary keys or a multi-column primary key. Try this query: `ALTER TABLE mytable CHANGE COLUMN AI AI INT(11) NOT NULL, DROP PRIMARY KEY;` — elixenide, Nov 16 '15 at 06:49
@EdCottrell That code returns: #1091 - Can't DROP 'PRIMARY'; check that column/key exists As for the buttons, why are some of them lit up then? That means they're primary, right? — Robbie, Nov 16 '15 at 06:57
@Robbie It sounds like you have already dropped the key. Based on your comment to ching's answer, you already tried something like the query I gave you. The server is telling you there isn't a primary key anymore. Try `SHOW KEYS FROM mytable`. There should be no results. And no, the buttons do *not* mean that those columns are primary keys. You can only have one primary key (though it can include multiple columns). Those key buttons are for you to *create* a key (a/k/a index) on a given column. — elixenide, Nov 16 '15 at 07:02
Yeah it's showing no results. But why are only some of the keys gold and some gray? — Robbie, Nov 16 '15 at 07:08

score 8 · Answer 1 · edited May 23 '17 at 12:07

8

Try this

ALTER TABLE tablename MODIFY fieldname INT NOT NULL;

ALTER TABLE tablename DROP PRIMARY KEY;

Or you can check here

edited May 23 '17 at 12:07

Community

1
1

answered Nov 16 '15 at 06:29

ching

122
7

That was one of the first things I tried, but it won't let me do anything all it says is: Error #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key – Robbie Nov 16 '15 at 06:31
try see this http://stackoverflow.com/questions/2111291/remove-primary-key-in-mysql – ching Nov 16 '15 at 06:39
Is there not anyway to do it through the UI in phpmyadmin though? – Robbie Nov 16 '15 at 06:44
For me, `ALTER TABLE tablename DROP PRIMARY KEY;` worked fine in phpMyAdmin. – OuzoPower Mar 17 '19 at 17:04

score 0 · Answer 2 · answered Dec 27 '21 at 19:57

0

As @ching mentioned, since there is only one primary key in the table , you can run this command :

ALTER TABLE tablename DROP PRIMARY KEY;

Good luck.

answered Dec 27 '21 at 19:57

Hesam Moosapour

510
5
12

score 0 · Answer 3 · answered Apr 02 '23 at 18:24

Drop Primary Key ERROR 1075 solution

I had the same ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key of @Robbie

kindly @munjal-mayank linked a stakoverflow question/reply but in my kind opinion not the best one

Reading carefully the accepted answer https://stackoverflow.com/a/2111324/3446280

it does explain that the AUTO_INCREMENT is the issue

So I removed it

After the removal the ALTER TABLE tablename DROP PRIMARY KEY; worked fine without ERROR 1075 (42000): Incorrect table definition

How to remove primary keys in phpmyadmin

3 Answers3

Drop Primary Key ERROR 1075 solution