update doesn't work well without primary key

I have a table with unique key and a auto increased column defined as Key: CREATE TABLE testtable ( Seq INT(11) NOT NULL AUTO_INCREMENT, ID INT(11) NULL DEFAULT NULL, Data INT(11) NULL DEFAULT NULL, UNIQUE INDEX ID (ID) USING BTREE, INDEX Seq (Seq) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB;

when trying to update the it seems to generate an update command that includes all the columns values in the where: UPDATE test.testtable SET Data='13' WHERE Seq=1 AND ID=1 AND Data=12 LIMIT 1; SELECT Seq, ID, Data FROM test.testtable WHERE Seq=1 AND ID=1 AND Data=13 LIMIT 1;

I expected the update to "check" only the primary key (ID) in the where clause: UPDATE test.testtable SET Data='13' WHERE ID=1 LIMIT 1;

the problem is that when I have triggers (in my more complex solution) changing data on one columns updates data on another column via the trigger, and in that case the full "Where" clauses no longer align with the actual data and the operation fails with "0 rows updated...." when I set the key to primary key: ALTER TABLE testtable CHANGE COLUMN ID ID INT(11) NOT NULL DEFAULT NULL AFTER Seq, ADD PRIMARY KEY (ID); the update "fixes" the "where" clauses: SELECT Seq, ID, Data FROM test.testtable WHERE ID=2;

I think that setting the where to its basic should work with the "Unique Key" as it works with the "Primary Key"

tnx.

Your "id" column allows NULL values, for which rows with a NULL in it are ambiguous. Change the "id" column to disallow NULLs, and HeidiSQL will use it.