CODEWITHSUNDEEP
mysqlphpmyadmin
Markers
Markers

Reputation: 348

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

Why does phpMyAdmin give me this warning , and lack of function, when selecting from a table named only in lowercase (and underscores) that does have a (single column) primary key? I checked these elements after seeing this

Specifically my query is

SELECT su.* FROM `r8u2d_comps_testsubitem` su 
JOIN `r8u2d_comps_testitem` ti ON ti.id=su.testitemid 
JOIN `r8u2d_comps_test` t ON ti.testid=t.id 
WHERE t.id=241
ORDER BY ti.ordering

The table aliased as "su" has a column "id" (int(11), autoincrement) and a primary key using only this field. It looks to me like this query avoids all the restrictions listed in this answer, so what's the problem? Is it phpMyAdmin (my hosting company has 4.7.9, but I get the same problem locally with 5.0.4) or MySQL (host has 5.7.29-0ubuntu0.16.04.1 - (Ubuntu), I have 10.4.17-MariaDB - MariaDB Server, not strictly comparable I suppose).

Table structure

`id`            INT NOT     NULL AUTO_INCREMENT, 
`testitemid`    INT NOT     NULL
`marker`        CHAR(20)    NULL
`text`          TEXT        NOT NULL,
`ordering`      TINYINT     NOT NULL,
PRIMARY KEY (`id`),
KEY `testitemid` (`testitemid`),
KEY `ordering` (`ordering`),
CONSTRAINT `subelementToElement` 
    FOREIGN KEY (`testitemid`) REFERENCES `#__comps_testitem`(`id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION

Upvotes: 0

Views: 223

Answers (1)

Isaac Bennetch
Isaac Bennetch

Reputation: 12442

phpMyAdmin makes an effort to work with a primary/unique key for the purposes of enabling grid editing, but that detection logic doesn't hold up very well when using with multiple JOIN statements. It gets difficult for the phpMyAdmin parser to work backwards through some queries and determine which columns come from which tables and whether there's a primary key that could be used for editing the data. I suppose the warning message could better be written as something like "This table or query does not contain a unique column, or your query is a join that obfuscates the original table structure enough that we don't want to risk damaging your data."

Unfortunately, aside from someone rewriting this part of phpMyAdmin, the best solution I can recommend right now is to find the data you want to modify through your JOIN query then open that individual table and scroll through the Browse view to (or use Search to find) the row you wish to modify from the table directly.

Upvotes: 1

Related Questions