I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.
My table looks like this:
CREATE TABLE `my_table` ( `id` bigint(20) NOT NULL, `col1` bigint(20) NOT NULL, `col2` bigint(20) NOT NULL, `col3` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) )
I want the three columns
col3 to be unique. But a normal unique constraint
UNIQUE KEY `unique_constraint` (`col1`,`col2`,`col3`)
still allows me to insert identical rows whose
NULL. I want to forbid entries like these:
1 | 1 | 1 | NULL 2 | 1 | 1 | NULL
I was searching for a partial unique constraint in MySQL as proposed in the post above but apparently there are not supported in MySQL 5.5.
Can somebody give me a hint how to solve this best in mysql, I am also hoping there will be a performant solution!
Thanks from a newbie in mysql (coming from PostgreSQL)!