CODEWITHSUNDEEP
mysqlsql
Andrewboy
Andrewboy

Reputation: 364

Table with unique column+nullable column

I have the following problem:i have to store a date but because of only the year can be determined the month and the day are nullable.The Problem is when i insert a new row with the same year and null for the month and null for the day it will be a duplicate entry

create table date('id' int(10) not null auto_increment, 'Year' int(4) not null, 'Month' int(2) default null, 'Day' int(2) default null, Primary key('id'), Unique Key 'Year');

insert into date ('id','Year','Month','Day')

values (1,1990,NuLL,NULL), (2,1992,NULL,NULL), (3,1990,NULL,NULL);

Even the unique constraint it will be two entries for 1990 with NULL month and NULL day.

Please help me,i was try to find solution for it but maybe i used the wrong keywords but didn't find.Thank you for your help

Upvotes: 0

Views: 39

Answers (1)

grossvogel
grossvogel

Reputation: 6782

If you set up your unique key to span all three columns, then the only rows that will violate the constraint are those where all three (Day, Month, Year) are non-null and identical.

From the docs:

This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

Upvotes: 1

Related Questions