Re-evaluate `xxx(ZeroOr)OneByCandidateKey()`

[AnyhowStep]

trafficstars

Candidate keys correspond closely to UNIQUE keys on SQL databases.

Columns of UNIQUE keys are allowed to contain NULL values.

All xxx(ZeroOr)OneByCandidateKey() functions/methods use eqCandidateKey() internally.

eqCandidateKey() acknowledges that columns may be nullable. So, it uses null-safe equality checks.

---

The problem here is that we may have the following rows,

UNIQUE (id, val)

(1, NULL),
(1, NULL)

Because NULLs are considered distinct by databases like MySQL, we do not get a unique key violation error.

However, attempting to fetch one row by the candidate key (1, NULL) will give us two rows instead.

---

Proposals

• Disallow nullable columns in .addCandidateKey(); we already do this for .setPrimaryKey()
• Disallow null for candidate key arguments; you can fetch a row by candidate key (1, 1337) but you cannot fetch a row by candidate key (1, NULL)

The second proposal seems better

[AnyhowStep]

On MySQL, PostgreSQL, SQLite, the following,

CREATE TABLE test (
    testId INTEGER ,
    testVal INTEGER,
    CONSTRAINT u UNIQUE (testVal)
);
INSERT INTO test(testId, testVal) VALUES (NULL, NULL), (NULL, NULL);
SELECT * FROM test;

Will give the result set,

testId	testVal
NULL	NULL
NULL	NULL

---

As you can see, NULLs are treated as distinct values.

[AnyhowStep]

updateAndFetch(ZeroOr)OneByXxx() methods should not allow setting a primary/candidate key column to null

It should be fine for other columns (like a non-key column of a super key) to be set to null, though. Because the candidate key columns are the important bits that determine whether the row is truly unique.