3

My questions is whether it's possible to have unique index comprised of multiple columns where one of the columns may contain NULL.

Example: A table named 'regulated_person' has a number of columns including LAST_NAME, FIRST_NAME, BIRTH_DATE and ALIAS. The data type for BIRTH_DATE is date, for the others it's String/VARCHAR. LAST_NAME, FIRST_NAME and BIRTH_DATE are not nullable, i.e. they require values. ALIAS serves as a tie-breaker for cases where two or more people have the same first/last names and are born the same day. Because the tie-breaking situations are not common I want to avoid the need to provide an ALIAS value unless it's necessary.

By way of background the primary key for the table is handled by an auto-increment generator. The purpose for the unique index is to provide a 'business key' that will identify the row without having to resort to the primary key. I'm working with MySQL 5.1.35 and Hibernate ORM ver. 4.3.10.

Any suggestions as to server/database providers apart from MySQL would be welcome.

Thanks in advance for any guidance, and also apologies in advance for any unintended violations of 'forum protocol' as this is the first time I've submitted a question ANYWHERE.

3

Since the alias column is nullable, if you add a unique constraint on the composite (last_name, first_name, birth_date, alias), there will still be duplicates allowed, with the same values in the first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear but you can test the behaviour:

  • A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

What you could do is to define the alias as not null and add a default value, (say 'NONE' or 'DEFAULT' or the empty string ''). You (or the user) will not have to provide that value, it will be automatically saved in all rows. Once someone tries to add a new row with same last name, first name and birth date as an existing row, the unique constraint will forbid it. I guess you could add some procedure at that point, that asks for a different value for the alias and adds the new row with it.

  • Thanks, this is helpful and mirrors what I suspected. As an aside there is a lot of documentation on this but none that's as clear as it should be and as your reply, which I'm assuming is based on actual experience which beats theory every time. Suggestion about providing non-null default values is well-taken, will have to decide whether to do this in the database or the app, but in any case I know now what can/can't be done and that is VERY helpful. – jfr Aug 25 '15 at 16:08
  • This is interesting. With SqlServer I believe the behavior is opposite, ie if you throw a unique constraint on a nullable column you are only allowed one null. – Erik Aug 25 '15 at 16:38
  • @Erik yes, SQL-Server has implemented UNIQUE constraints differently regarding this (NULL) aspect (and differently than the SQL standard - which dictates that a NULL in a column bypasses UNIQUE constraints.) – ypercubeᵀᴹ Aug 25 '15 at 17:07
  • Yeah, build an extra column for uniqueness, and populate with an algorithm that suitably ignores (or turns into "") any NULLs. – Rick James Aug 26 '15 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.