MySQL unique clustered constraint not constraining as expected

Question

I'm creating a table with:

CREATE TABLE movies
(
 id     INT       AUTO_INCREMENT PRIMARY KEY,
 name   CHAR(255) NOT NULL,
 year   INT       NOT NULL,
 inyear CHAR(10), 
 CONSTRAINT UNIQUE CLUSTERED (name, year, inyear)
);

(this is jdbc SQL)

Which creates a MySQL table with a clustered index, "index kind" is "unique", and spans the three clustered columns:

full size

However, once I dump my data (without exceptions thrown), I see that the uniqueness constraint has failed:

SELECT * FROM movies
WHERE name = 'Flawless' AND year = 2007 AND inyear IS NULL;

gives:

id,     name,       year, inyear
162169, 'Flawless', 2007, NULL
162170, 'Flawless', 2007, NULL

Does anyone know what I'm doing wrong here?

Answer 1

MySQL does not consider NULL values as equal; hence, why the unique constraint appears to not be working. To get around this, you can add a computed column to the table which is defined as:

nullCatch as (case when inyear is null then '-1' else inyear)

Substitute this column in for 'inyear' in the constraint:

 CONSTRAINT UNIQUE CLUSTERED (name, year, nullCatch)