At 11:43 +0200 5/2/03, Rene van de Weerd wrote:
>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.