MySQL Workbench: Edit Read-Only Table Data

1. Create a Read-Only Table Using MySQL Workbench
2. Fix the Read-Only Table in MySQL Workbench

This article will teach how to edit read-only table data in MySQL Workbench. First, we’ll create the read-only table and show you how to edit it.

We’ll do all these using MySQL 8.0.29 and MySQL Workbench 8.0 Community Edition (CE).

Create a Read-Only Table Using MySQL Workbench

Open MySQL Workbench and create a database named testing_workbench_readonly. If you already have a database with a read-only table, skip to the next section.

Otherwise, create a table in the new database using the following SQL.

CREATE TABLE sample_users (
    user_id INT NOT NULL,
    username VARCHAR(20) NOT NULL,
    user_first_name VARCHAR(50) NOT NULL,
    user_last_name VARCHAR (50) NOT NULL
) ENGINE = InnoDB;

The following is the SQL in MySQL Workbench.

Now, hold down Ctrl and press Enter to execute the SQL. This will create the database table.

Next, insert records into this table using the following SQL.

INSERT INTO sample_users (user_id, username, user_first_name, user_last_name) VALUES (1, 'mrdoe', 'Mister', 'Doe');
INSERT INTO sample_users (user_id, username, user_first_name, user_last_name) VALUES (2, 'user543', 'John', 'Nathaniel');
INSERT INTO sample_users (user_id, username, user_first_name, user_last_name) VALUES (3, 'frank44', 'Frank', 'Martins');
INSERT INTO sample_users (user_id, username, user_first_name, user_last_name) VALUES (4, 'valery12', 'Cynthia', 'Valerie');

The following is the SQL in MySQL Workbench.

Hold down Ctrl, Shift, and Enter to execute the queries simultaneously. Now, check the records in the table.

SELECT * FROM sample_users;

You’ll get the following image where it shows the table is read-only.

Fix the Read-Only Table in MySQL Workbench

Move your mouse over the "Read Only" text to fix the read-only table in MySQL Workbench. A tooltip will appear that shows what’s wrong.

The tooltip shows that the table needs a unique row identifier. An example of such an identifier is a primary key.

As it stands, our table does not have a primary key. If you add a primary key to the table, MySQL Workbench will allow you to edit it.

Now, add the primary key using the following SQL.

ALTER TABLE sample_users MODIFY user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Hold down Ctrl and press Enter to execute the SQL. You can edit the SQL afterward when you run it without getting an error.

Before that, run a SELECT query to view the table data. The "Read Only" text has changed to a disabled Apply button.

That means the button will become active when you change the table.

You can activate the Apply button by modifying the table data and pressing the Enter key. Then click on the Apply button to save your changes.

This will show a dialog box where you’ll see the SQL query for your changes. Click the Apply button to run the query, then click the Finish to close the window.