List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 2 2003 1:39pm
Subject:Re: UNIQUE and NULL values
View as plain text  
At 11:43 +0200 5/2/03, Rene van de Weerd wrote:
>Hi all,
>I have created a unique index on two columns: (int,varchar(5)).
>My understanding would be that this would force every combination of 
>the int and the varchar to be unique.
>But I found out that this isn't the case for NULL values.
>I can insert 1,NULL as many times as I want.
>In contradiction, I can only insert 1,"test" once.
>Is this normal behaviour?

For MyISAM and InnoDB tables, you can insert multiple NULL
values into a UNIQUE index.   For BDB tables, you can insert a
single NULL value into a UNIQUE index.

If you consider the case of tables with a foreign key relationship,
allowing multiple NULL values if a requirement when you have a
child table with a UNIQUE foreign key that is set to ON DELETE SET NULL.
Were this not the case, you couldn't delete more than one row from
the parent table.

>Kind regards,

Paul DuBois
sql, query
UNIQUE and NULL valuesRene van de Weerd2 May
  • re: UNIQUE and NULL valuesVictoria Reznichenko2 May
  • Re: UNIQUE and NULL valuesBruce Feist2 May
  • Re: UNIQUE and NULL valuesPaul DuBois2 May