[FIXED] How To Correct A Unique Key To Not Allow Duplicate Entries

Issue

Here is my problem: The Key "idx_SR_u_Identity_FingerPrintProfile" is meant to constrain the fields "c_r_Fingerprint" and "c_r_Profile" to be unique.

It seems that I have done something wrong because all 4 entries in the table have identical values for those two fields. It is okay if two records have the same Fingerprint OR the same Profile, but not BOTH.

How can I correctly specify this unique key, so that such duplicates are not allowed?

_{(source: Rigel222.Com)}

Solution

I think your key is correct, but MySQL does not apply it to NULL values. The MySQL Docs for CREATE TABLE state:

a UNIQUE index allows multiple NULL values for columns that can contain NULL.

While entries like (1,2) can occur only once, entries like (1,NULL) can occur several times, they are not considered to be duplicates because of the NULL.

Dependent on your use case, you may forbid NULL for the two columns to circumvent the problem.

Answered By - titanoboa