CODEWITHSUNDEEP
mysql
user2465936
user2465936

Reputation: 1040

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

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

Upvotes: 0

Views: 2015

Answers (2)

Tasos P.
Tasos P.

Reputation: 4114

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,'')

Upvotes: 1

juergen d
juergen d

Reputation: 204746

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.

Upvotes: 3

Related Questions