[mysql] MySQL Workbench Edit Table Data is read only

When trying Edit Table Data in MySQL Workbench 5.2.37, its in read only mode.

It is editable only if the table has a primary key.

Is there any fix to deal with table without primary key??

Thanks

As one of the suggestion I tried upgrading WB 5.2.40. But still this issue exists.. Could any one Help please..

Yes, I found MySQL also cannot edit result tables. Usually results tables joining other tables don't have primary keys. I heard other suggested put the result table in another table, but the better solution is to use Dbeaver which can edit result tables.

Hovering over the icon "read only" in mysql workbench shows a tooltip that explains why it cannot be edited. In my case it said, only tables with primary keys or unique non-nullable columns can be edited.

I was getting the read-only problem even when I was selecting the primary key. I eventually figured out it was a casing problem. Apparently the PK column must be cased the same as defined in the table. using: workbench 6.3 on windows

Read-Only

SELECT leadid,firstname,lastname,datecreated FROM lead;

Allowed edit

SELECT LeadID,firstname,lastname,datecreated FROM lead;

According to this bug, the issue was fixed in Workbench 5.2.38 for some people and perhaps 5.2.39 for others—can you upgrade to the latest version (5.2.40)?

Alternatively, it is possible to workaround with:

SELECT *,'' FROM my_table

If you set a default schema for your DB Connection then Select will run in readonly mode until you set explicitly your schema

USE mydb;
SELECT * FROM mytable

this will also run in edit mode:

SELECT * FROM mydb.mytable 

(MySql 5.2.42 / MacOsX)

I hope this helps.

Uncheck the marked check, it will enable the grid edit

This is the Known limitation in MySQLWorkbench (you can't edit table w/o PK):

To Edit the Table:

Method 1: (method not working in somecases)
right-click on a table within the Object Browser and choose the Edit Table Data option from there.

Method 2:
I would rather suggest you to add Primary Key Instead:

ALTER TABLE `your_table_name` ADD PRIMARY KEY (`column_name`);

and you might want to remove the existing rows first:

Truncate table your_table_name

if the table does not have primary key or unique non-nullable defined, then MySql workbench could not able to edit the data.

MySQL will run in Read-Only mode when you fetch by joining two tables and columns from two tables are included in the result. Then you can't update the values directly.

Guided by Manitoba's post, I found another solution. As a summary, the solutions are:

1. With a USE command

   USE mydb;
   SELECT * FROM mytable
   

2. With an explicit schema prefix:

   SELECT * FROM mydb.mytable
   

3. GUI

   On Object Browser "SCHEMAS" pane, all database icons are initially not highlighted if you have the same issue. So you can right click on the database icon you wanted to be the default, select "Set as default schema".

If your query has any JOINs, Mysql Workbench will not allow you to alter the table, even if your results are all from a single table.

For example, the following query

SELECT u.* FROM users u JOIN passwords p ON u.id=p.user_id WHERE p.password IS NULL;

will not allow you to edit the results or add rows, even though the results are limited to one table. You must specifically do something like:

SELECT * FROM users WHERE id=1012;

and then you can edit the row and add rows to the table.

1.)You have to make the primary key unique, then you should be able to edit.

right click on you table in the "blue" schemas ->ALTER TABLE, look for your primert key (PK), then just check the check-box, UN, the AI should already be checked. After that just apply and you should be able to edit the table data.

2.)You also need to include the primery key I your select statement

Nr 1 is not really necessary, but a good practice.

In MySQL Workbench you need an INDEX to edit, no need it to be PK (although adding a PK is a solution as well).

You can make a regular INDEX or compound INDEX. That's all MySQL WB needs to fix the Read only thing (I have v. 6.2 with MariaDB v. 10.1.4):

Just right click table, select "Alter table..." then go to "Indexes" tab. In the left pane put a custom name for your index, and in the middle pane checkmark one (make sure the vale will be unique) or more fields (just make sure the combination is unique)