Repository navigation
HeidiSQL cannot edit query results using a declared unique key #2600
Description
Description
Issue: HeidiSQL cannot edit query results using a declared unique key
Environment:
- HeidiSQL:
12.21.0.7344 - OS: Windows 11, version
25H2
Description:
HeidiSQL reports:
Selected columns don't contain a sufficient set of key columns to allow editing.
The table has both a primary key and a unique key:
PRIMARY KEY (`id`),
UNIQUE KEY `event_id` (`event_id`)The following query is considered non-editable by HeidiSQL, even though event_id is NOT NULL and has a unique index:
SELECT event_id, event_name, event_type, severity, host_name, ip,
duration, status, alerts, created_at, updated_at
FROM example_alert_history
WHERE alerts < 2
AND host_name = 'example-host'
ORDER BY updated_at DESC
LIMIT 300;However, DBeaver can edit the result using the exact same query and database account.
Adding the primary-key column id to the SELECT list makes the result editable in HeidiSQL. This suggests that HeidiSQL may not recognize a valid secondary unique key when determining whether a query result can be edited.
Could HeidiSQL support NOT NULL secondary unique keys when identifying editable query results?
Simplified table definition:
CREATE TABLE `example_alert_history` (
`id` int NOT NULL AUTO_INCREMENT,
`event_id` varchar(255) NOT NULL,
`event_name` varchar(255) NOT NULL,
`event_type` varchar(255) NOT NULL,
`severity` varchar(255) NOT NULL,
`host_name` varchar(255) NOT NULL,
`alerts` int NOT NULL DEFAULT 0,
`updated_at` timestamp NULL,
`created_at` timestamp NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `event_id` (`event_id`)
);Workaround: Adding id to the SELECT list allows HeidiSQL to edit the result.
HeidiSQL version and OS
HeidiSQL: 12.21.0.7344 - OS: Windows 11, version 25H2
Database server version
11.8.8-MariaDB
Reproduction recipe
Reproduction recipe
- Connect to a MariaDB server using HeidiSQL
12.21.0.7344on Windows 1125H2. - Create a test table:
CREATE TABLE `example_alert_history` (
`id` INT NOT NULL AUTO_INCREMENT,
`event_id` VARCHAR(255) NOT NULL,
`event_name` VARCHAR(255) NOT NULL,
`event_type` VARCHAR(255) NOT NULL,
`severity` VARCHAR(255) NOT NULL,
`host_name` VARCHAR(255) NOT NULL,
`ip` VARCHAR(100) NOT NULL,
`duration` VARCHAR(255) NOT NULL DEFAULT '0',
`status` VARCHAR(100) NOT NULL DEFAULT 'open',
`alerts` INT NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `event_id` (`event_id`)
) ENGINE=InnoDB;- Insert sample data:
INSERT INTO `example_alert_history`
(`event_id`, `event_name`, `event_type`, `severity`,
`host_name`, `ip`, `duration`, `status`, `alerts`)
VALUES
('100001', 'Example event', 'Example type', 'Warning',
'example-host', '192.0.2.10', '0', 'open', 0);- Execute this query in HeidiSQL:
SELECT
event_id,
event_name,
event_type,
severity,
host_name,
ip,
duration,
status,
alerts,
created_at,
updated_at
FROM example_alert_history
WHERE alerts < 2
AND host_name = 'example-host'
ORDER BY updated_at DESC
LIMIT 300;- Click or double-click a cell in the result grid to edit it.
- HeidiSQL displays:
Selected columns don't contain a sufficient set of key columns to allow editing.
Please select primary or unique key columns, or just all columns.
- Add the primary-key column
idto theSELECTlist and execute the query again:
SELECT
id,
event_id,
event_name,
event_type,
severity,
host_name,
ip,
duration,
status,
alerts,
created_at,
updated_at
FROM example_alert_history
WHERE alerts < 2
AND host_name = 'example-host'
ORDER BY updated_at DESC
LIMIT 300;The result becomes editable.
- The original query can be edited successfully in DBeaver using the same database account and connection permissions.
Error/Backtrace
No backtrace is available. HeidiSQL displays the following message in the result grid:
Selected columns don't contain a sufficient set of key columns to allow editing.
Please select primary or unique key columns, or just all columns.
The table contains a `NOT NULL` unique key, `event_id`, and the original query includes that column. Adding the primary-key column `id` works around the problem.
Activity