Remember that nullable unique columns can contain multiple NULL values
A unique external-reference column prevents duplicate populated references, but several rows with NULL can still exist. In MySQL, a unique index permits multiple NULL values. That can be correct for an optional reference, or a mismatch with a rule that every record must have an identity.
Separate presence from uniqueness
CREATE TABLE provider_events (
id BIGINT UNSIGNED PRIMARY KEY,
external_id VARCHAR(100) NOT NULL,
UNIQUE KEY provider_events_external_unique (external_id)
);
This example makes the reference mandatory. If several providers can reuse the same ID, include the provider identity in the unique key rather than requiring a globally unique external string.
Empty strings are another value
Replacing missing references with an empty string makes all missing rows compete for the same ordinary value under a unique index. That is not a meaningful identity policy. Validate the upstream contract and decide whether incomplete events belong in a separate rejected or staging state.
Test duplicate populated references, missing references, and references from different providers. Confirm the validator and database agree about whether absence is allowed.
When adding a new unique index to existing data, inspect duplicates first and resolve them with a documented rule. Do not delete arbitrary rows simply because the migration fails. A failed constraint often reveals an ambiguity in the business identity that should be resolved before the database can enforce it reliably.