MySQL Forums
Forum List  »  InnoDB

Unique indexes and NULL column values
Posted by: Al
Date: November 08, 2005 07:27PM

It appears that NULL column values in unique indexes are treated differently depending upon which storage engine is used. Using BDB, the NULL is treated as a unique index value. With InnoDB and MyISAM, it is not. (I believe that Oracle behaves the same as BDB). Why the disparity? Shouldn't all engines behave identically in this respect?

The following table and index illustrates a problem I'm having using InnoDB. The Channel table contains communication frequency data.

create table CHANNEL
(
CHANNEL_KEY int not null AUTO_INCREMENT,
TX_FREQ double,
RX_FREQ double,
FREQ_ID varchar(255),
FREQ_DESCRIPTION varchar(255),
primary key (CHANNEL_KEY)
)
type = InnoDB;

create unique index CHANNEL_SK on CHANNEL
(
TX_FREQ,
RX_FREQ
);

I created a multi-column unique index on TX_FREQ and RX_FREQ to prevent duplicate records with the same Tx and Rx frequencies. However, either the Tx or Rx frequency columns could have NULL values (in cases where the communications channel is Tx Only or Rx Only).

The unique index constraint correctly prevents records from being inserted when both the Tx_Freq and Rx_Freq columns have non-null values. However, if either the Tx_Freq or Rx_Freq column has a null value, duplication occurs. For example, multiple records where TX_FREQ = 155.25 and RX_FREQ is NULL is allowed -- not what I want.

Is there a way for InnoDB to behave the same as BDB when it comes to NULLs in unique indexes?

Options: ReplyQuote


Subject
Views
Written By
Posted
Unique indexes and NULL column values
25794
Al
November 08, 2005 07:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.