0

I have a unique index on my table, the unique index is on all 4 columns.

 firstname VARCHAR NOT NULL
 lastname VARCHAR NOT NULL
 dateofbirth DATE NULL
 reference VARCHAR NULL

From what I have read (and recently discovered), is that mysql does not enforce uniqueness for columns with NULL value.

In my case firstname and lastname area always populated, but dateofbirth and reference are optional, and set to null by default.

I have changed reference column from NULL to NOT NULL default value '', but how can I do the same for date of birth without actually changing the column type to a VARCHAR ?

bumperbox
  • 10,166
  • 6
  • 43
  • 66
  • Are you trying to create a compound index, on FirstName + LastName + Birthdate? Or just BirthDate? The question doesn't make sense as it's written. – Dmitriy Khaykin Feb 14 '14 at 03:42
  • There should be no problem creating an index on a null-allowed column: http://stackoverflow.com/a/1346776/575376 – juergen d Feb 14 '14 at 03:42
  • @juergend that link is what i am trying to avoid. I want each row to be unique in the sense that NULL would be considered 1 value, and 2 rows will NULL values would cause an duplicate constraint error. Which is why I converted reference to blank by default instead of null – bumperbox Feb 14 '14 at 03:50
  • 1
    just like reference. column change dateofbirth column as not null default '0000-00-00'. – ursitesion Feb 14 '14 at 03:54
  • What sense does it make to allow only one person to have no birthdate set? – juergen d Feb 14 '14 at 03:55
  • @juergend The date of birth is only required for some of my clients. The other clients complain when they have to type in extra information that they don't need to know, so that is why date of birth is an optional field. – bumperbox Feb 14 '14 at 16:44
  • @bumperbox: Sure, that's why **multiple** null values should not be a problem. But allowing only a **single** null value as you described it in your previous comment, does not make sense. – juergen d Feb 14 '14 at 16:46
  • @juergend I get where you are coming from now. – bumperbox Feb 14 '14 at 16:53

1 Answers1

2

You could set a default value for dates:

ALTER TABLE tablename MODIFY dateofbirth DATE NOT NULL DEFAULT '0000-00-00';

Doing this will generate a warning, which you can ignore:

Data truncated for column 'dateofbirth' at row xyz

After the change, any values that were NULL before are now set to the new default value.

Alternatively, find another column that will always be filled in and can distinguish two people with the same first and last name.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • Thanks @Jack. I was being lazy and kind of hoping there was another solution, as now I will need to go through and change a lot of sql so that my reports aren't filled with 0000-00-00 all over the place. – bumperbox Feb 14 '14 at 16:41