Peeeech Peeeech - 4 months ago 15
MySQL Question

Table with no unique column in phpMyAdmin

Here is my table:

CREATE TABLE group_edits (
vfile_id bigint(20) unsigned NOT NULL,
editor_id int(10) unsigned NOT NULL,
edits text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


There is no unique index, because one editor_id can edit multiple vfile_ids, also one vfile_id can be edited by multiple editor_ids.

phpMyAdmin 4.1.6 does not allow me to edit this table saying:

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


Now it makes me thing is there anything wrong with the table like this? Rows are unique when you take a value of editor_id AND vfile_id while none of the columns alone is unique.

I know that to fix it I could add

`ID` int(11) NOT NULL AUTO_INCREMENT,


but it does not reflect the design of my database schema and I would like to avoid adding tricks just to make phpMyAdmin work.

Is my database design wrong or is it phpMyAdmin?

Answer

If you are absolutely sure you can make a unique primary compound key of (editor_id, vfile_id), go ahead and do that.

 ALTER TABLE group_edits ADD PRIMARY KEY (editor_id, vfile_id)

You'd be wise to use phpmyadmin to dump a copy of the table first, so you can restore it if you mess it up.

That should permit phpmyadmin to let you update rows in the table.