The Ultimate Guide to High Availability with MariaDB

Behavior with composite unique keys

Hi,

My table has a composite unique key consisting of three columns (col1, col2, col3), where one column (col3) has a default value of NULL. The table is accepting duplicate records when the column with the default NULL value (col3) is NULL, even if the other column values are the same.

For example:

col1 col2 col3 col4 A1 B1 NULL Z1 A1 B1 NULL Z2 Is this expected behavior? If it is an issue, will it be addressed in higher versions of MariaDB? My current version is 10.7.3.

Any help would be appreciated.

Thank you, Harinath

Answer Answered by Marko Mäkelä in this comment.

Yes, this is expected, because NULL is not equal to NULL. MDEV-17295 is a little related to this.

By the way, MariaDB Server 10.7 was a short-term supported release. You should consider upgrading to the long-term support releases 10.11 or 11.4.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.