This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available

Question

The error generated in phpMyAdmin is:

This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

What does this error message mean and how can I fix it?

Answer 1

I have been faced with this problem.

The cause is your table doesn't have a primary key field.

And I have a simple solution: Set a field to primary key to specific field that suits your business logic.

For example, I have database thesis_db and field thesis_id, I will press button Primary (key icon) to set thesis_id to become primary key field:

Answer 2

This is not an error. PhpMyAdmin is just informing you, that there is no unique ID column in your result set. Depending on the type of query you sent, this is the desired behaviour.

It is not MySQL which is saying it needs a unique ID, if any combination of the columns in your result set is unique, the values of those columns can be used in an UPDATE or DELETE query. It is phpMyAdmin which says it does not have enough information to offer you the checkboxes and buttons you will normally see in a result set with unique ID.

Answer 3

Simply create a new column, set the Name to whatever you like, set the Type to INT and check the box that says A_I.

The A_I checkbox stands for AUTO_INCREMENT, which essentially means that sequence numbers are assigned automatically in that new column (see below).

 column1 | column2 | id
-----------------------
 value   | value   | 1
-----------------------
 value   | value   | 2
-----------------------
 value   | value   | 3
-----------------------
 value   | value   | 4

This column essentially acts as a reference for phpMyAdmin to delete rows from. If necessary, click on the unique button for this new column, although this happened automatically for me. After following the above steps, you should no longer have the error message and buttons should appear for editing rows in phpMyAdmin!

Answer 4

My case is different. This issue is only specific to PHPMyAdmin. I downloaded couple other admin tools (Adminer, MySQLWorkbench, HeidiSQL etc) and the same db works fine in all of those.

I have all the indexes, primary key and unique keys defined and still get the error. I get this after I upgraded to MySQL 5.6 (did not face the same with the previous versions).

Turns out PMA has issues with Table names in capital. PMA is not able to recognise keys with capital table names. Once I change them to small (ALTER TABLE mytable ENGINE=INNODB -- I use INNODB -- does that for each table without changing anything else), I was able to access normally. I'm on a Windows system with UniformServer.

Answer 5

This is how you get rid of that notice and be able to open those grid cells for edit

1) click "STRUCTURE"

2) go to the field you want to be a primary key (and this usually is the 1st one ) and then click on the "PRIMARY" and "INDEX" fields for that field and accept the PHPMyadmin's pop-up question "OK".

3) pad yourself in the back.

Answer 6

An easy fix to this would be going to the SQL tab and just simply put in the code

ALTER TABLE `tablename`
ADD PRIMARY KEY (`id`);

Asuming that you have a row named id.

Answer 7

Adding this in the config.inc.php file worked for me (under the last $cfg line):

$cfg['RowActionLinksWithoutUnique'] = 'true';

The file should be located in the phpMyAdmin folder on your local computer

Answer 8

I have been faced this problem.

The cause is your table don't have a primary key field.

And I have a simple solution: Set a field to primary key to specific filed that suit with your business logic. For example, I have database thesis_db and field thesis_id, I will press button Primary (key icon) to set thesis_id to become primary key field

Answer 9

I recently got the same problem and after looking for duplicates I was able to fix it just by setting (missing) primary key on the table. Hope this could help

Answer 10

In my case, the error occured in phpmyadmin version 4.5.1 when i set lower_case_table_names = 2 and had a table name with uppercase characters, The table had a primary key set to auto increment but still showed the error. The issue stopped when i changed the table name to all lowercase.

Answer 11

I faced the same problem whenever i am firing SELECT query with specific columns, though the one of the column is primary in table. I was trying to fetch selective records with selective columns in standard wordpress "posts" table. ID column is already primary and unique..

Just specifying primary/unique column name will not resolve this issue. You have to specify full column name like "posts.id" (tablename.columnname) that tells PMA to select specific column and reveals edit etc..

My PMA is 4.3.8. Let me know if this helps..

Answer 12

the code that worked for me

ALTER TABLE `table name`
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);

Answer 13

This for sure is an old topic but I want to add up to the voices to crop maybe new ideas. To address the WARNING issue under discussions, all you need to do is to set one of your table columns to a PRIMARY KEY constraint.

Answer 14

This question helped me identify the problem of why phpMyAdmin refused me grid-edit-etc. on some tables. I just had forgotten to declare my primary key and was overseeing it in my "Why the hell should this table be different from its neighbours" solution search process...

I just wanted to react on following in OP self-answer:

The other table had multiple AI int values that were the Primary field, but there were multiple values of the same kind.

The simple fix for this was to just add a column to the end of the table as Unique AI Int. Basically all MySQL is saying is it needs a unique value in each record to differentiate the rows.

This was actually my case, but there's absolutely no need to add any column: if your primary key is the combination of 2 fields (ex. junction table in many to many relationship), then simply declare it as such:
- eiter in phpyAdmin, just enter "2" in "Create an index on [x] columns", then select your 2 columns
- or ALTER TABLE mytable ADD PRIMARY KEY(mycol1,mycol2)

Answer 15

I got this error when trying to modify directly after running Query. Turns out, after making a view from that exact same query, I was able to modify the values.

Answer 16

This for me was resolved by re-exporting the data from original source DB and then importing it into the mirror DB.

Answer 17

Make sure all your tables have one primary key. I forgot to add a primary key to one table and that solved this problem for me. :)