MySQL Unique constraint for Column does not allow insert multiple Null and empty. ENGINE=InnoDB

Question

With SHOW CREATE TABLE get following information

 (      
  `Number` int(11) NOT NULL AUTO_INCREMENT,
  `CompanyName` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `RegistrationNumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

   UNIQUE KEY `Number_2` (`Number`),
   UNIQUE KEY `CompanyName` (`CompanyName`),
   UNIQUE KEY `RegistrationNumber` (`RegistrationNumber`)

 ) ENGINE=InnoDB AUTO_INCREMENT=280 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

In RegistrationNumber need to allow insert multiple NULL and empty values.

However get error Integrity constraint violation: 1062 Duplicate entry '' for key 'RegistrationNumber'

Here http://forums.mysql.com/read.php?22,53591,53591 read that for InnoDB NULL is not treated as unique value.

Here https://stackoverflow.com/a/1346776/2465936 read that Unique constraint allows empty values.

Please, advice how to insert multiple empty values in RegistrationNumber Do I need to change something in RegistrationNumber varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,?

Or with ENGINE=InnoDB it is not possible? Changed to MyISAM. The same error...

Update

Oh.... going crazy

$sql = "INSERT INTO 18_6_TransactionPartners (RegistrationNumber) VALUES";

$insertQuery = array();
$insertData = array();

foreach ($num_row_1 as $i => $row) {
$insertQuery[] = '(?)';

if ( (strlen($data_b[$i])) > 0 ) {
echo '(strlen($data_b[$i])) > 0<br>';
$insertData[] = $data_b[$i];
}
else {
echo '??? null??? <br>';
$insertData[] = 'NULL';
}
}

And get QLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'NULL' for key 'RegistrationNumber'

What is wrong now?

Needed to change $insertData[] = 'NULL' to $insertData[] = NULL;

Finally all is clear. Thanks for answers

`null` isn't the same as empty string or `0`. a `null` is `null`. Period. If you have such a constraint, enter `null` value, not empty or 0 because they produce constraint failure. — N.B., Aug 09 '13 at 10:55

juergen d · Accepted Answer · 2013-08-09T10:40:17.267

3

It should work as you say.

But it looks like you insert an empty string and not null into your column. That is different. You can't have multiple empty strings in a unique column.

0 is not null. 0 is a value - zero. But null means no value.

edited Aug 09 '13 at 10:40

answered Aug 09 '13 at 10:34

juergen d

201,996
37
293
362

I tried to insert multiple `0` and get `SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key RegistrationNumber`. I trying to import excel file content in mysql – user2465936 Aug 09 '13 at 10:37
1

`0` is not `null`. `0` is a value - zero. But `null` means **no** value. – juergen d Aug 09 '13 at 10:38
1

Thanks. Will experiment (to understand)... seems starting to understand. If want to insert multiple `NULL` then can insert. If multiple ` ` or `0`, can not insert. So, see solution that empty string and `0` need to convert to `NULL` – user2465936 Aug 09 '13 at 10:42

score 1 · Answer 2 · answered Aug 09 '13 at 10:59

A Unique Constraint allows multiple Null values. In database context, empty value really means Null, since the empty string (or number zero etc) are perfectly known values. What you can do is use the NULLIF function and replace the empty strings with null (i.e. Nullif(myValue,'')

MySQL Unique constraint for Column does not allow insert multiple Null and empty. ENGINE=InnoDB

2 Answers2