I have a table with the following schema:

CREATE TABLE T (
    id BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    key BIGINT UNSIGNED NOT NULL,
    val BIGINT UNSIGNED NOT NULL,
);

I need to ensure that if the key is the same, the value must also be the same, i.e. you cannot have both “foo: bar” and “foo: bar2” in the table.

Note: foo and bar stand for numeric values in this question.

But 2x “foo: bar”, is allowed (“foo: bar”, and another “foo: bar”)

One way to do this is make key a unique constraint.

ALTER TABLE T ADD CONSTRAINT uk_key UNIQUE (key);

So there can only be 1 “foo: bar” in the table.
How can I safely allow insertion operations of 2 “foo: bar” in this case? i.e. both should ‘succeed’ even though the table has only 1 row.
I can try

SELECT * from T where key=foo FOR UPDATE;
--- if key is there, make sure it's equal to value, if so, success
--- else insert
INSERT INTO T VALUES (foo, bar);

But this isn’t entirely safe. The select wouldn’t lock anything if there’s no row. So the insert may fail with a duplicate key constraint anyway if there are interleaved transactions going on that both do select first, hold no locks and then insert.

This leads to the next solution where I rely on the duplicate constraint:

INSERT INTO T VALUES (foo, bar);
-- if no duplicates, success
-- if duplicates, select and check
SELECT val from T where key=foo;
-- check that val == bar

Is this safe? Would MySQL (InnoDB) hold the lock on the row after insert even if it failed due to duplicate error (It’s possible for row to be deleted after insert fail)? Also is there a better way to achieve my goal here?