4

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?

Peeech
  • 639
  • 1
  • 7
  • 15
  • I wouldn't call adding an `ID` column a "trick". That's what I would suggest in this scenario. – gen_Eric Feb 05 '14 at 15:41
  • @RocketHazmat Is there any reason to add `ID` column from the point of view of my application, not phpMyAdmin? – Peeech Feb 05 '14 at 16:39
  • 1
    See this: http://stackoverflow.com/questions/5187643/do-mysql-tables-need-an-id – gen_Eric Feb 05 '14 at 16:44
  • @RocketHazmat Yes, but I would never reverence this column in any query - that's why I hesitate if it would be of any use. Thanks for pointing to more reading! – Peeech Feb 05 '14 at 16:50
  • Possible duplicate of [Resolution: This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available](http://stackoverflow.com/questions/18922503/resolution-this-table-does-not-contain-a-unique-column-grid-edit-checkbox-ed) – Isaac Adni Jul 28 '16 at 14:34

1 Answers1

5

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Composite primary key, never knew about it. I see my question is quite old: http://stackoverflow.com/questions/4737190/composite-primary-key-or-not – Peeech Feb 05 '14 at 16:54