Alex Rivera | Logout

Stop MySQL tolerating multiple NULLs in a UNIQUE constraint

Asked 2012-02-14T16:41:56.863
10

My SQL schema is

CREATE TABLE Foo (
 `bar` INT NULL ,
 `name` VARCHAR (59) NOT NULL ,
 UNIQUE ( `name`, `bar` )
) ENGINE = INNODB;

MySQL is allowing the following statement to be repeated, resulting in duplicates.

INSERT INTO Foo (`bar`, `name`) VALUES (NULL, 'abc');

despite having

UNIQUE ( `name`, `bar` )

Why is this tolerated and how do I stop it?

mysql sql mysql5
Edit
Report

1 Answer

14

Warning: This answer is outdated. As of MySQL 5.1, BDB is not supported.

It depends on MySQL Engine Type. BDB doesn't allow multiple NULL values using UNIQUE but MyISAM and InnoDB allows multiple NULLs even with UNIQUE.

answered 2012-02-14T16:49:04.367

Your Answer