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?
As one of the suggestion I tried upgrading WB 5.2.40. But still this issue exists.
I'm assuming the table has a primary key. First try to run a unlock tables command to see if that fixes it.
If all else fails you can alter the table to create a new primary key column with auto-increment and that should hopefully fix it. Once you're done you should be able to remove the column without any issues.
As always you want to make a backup before altering tables around. :)
Note: MySQL workbench cannot work without a primary key if that's your issue. However if you have a many to many table you can set both columns as primary keys which will let you edit the data.
if the table does not have primary key or unique non-nullable defined, then MySql workbench could not able to edit the data.
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.
This is the Known limitation in MySQLWorkbench (you can't edit table w/o PK):
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
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;
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)
If your table is in read-only mode, checking first is the primary key. If the primary key is not defined, add as the following,
ALTER TABLE
scema_here.table_name_here ADD PRIMARY KEY (your primary key here);
Cheers ! :)
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
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.
Guided by Manitoba's post, I found another solution. As a summary, the solutions are:
With a USE command
USE mydb;
SELECT * FROM mytable
With an explicit schema prefix:
SELECT * FROM mydb.mytable
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.
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.
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.
The fundamental issue is that mysql will not let you edit a table unless it has a primary key (if you've made it to this post then you likely know that by now).
So solution 1 is: If you have a Primary Key and have not declared it, declare it, click apply and edit the table
But the problem that I ran into was different and I wish this post had been there.
I had created a table to show the many-to-many relationship between two tables. The table consisted of two columns (Table 1 PK and Table 2 PK) but neither of these columns could be the primary key of this join table and I didn't want to create a 3rd column just for a PK.
The solution was to declare BOTH columns as PK! That way the table checks to make sure the combination of col 1 and col 2 are unique AND then I was able to edit the table.
The obvious bonus is that now mySQL will check that I don't enter a duplicate since the combination must always be unique.
Uncheck the marked check, it will enable the grid edit
