Modify Column vs Drop and Add Column Mysql

table structure -

| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| candidateId    | bigint(20)   | NO   |     | NULL    |                |
| profileId      | bigint(20)   | NO   | MUL | NULL    |                |
| clientId       | bigint(20)   | NO   |     | NULL    |                |
| email          | varchar(128) | NO   |     | NULL    |                |
| verified       | tinyint(1)   | YES  |     | 0       |                |
| isPrimary      | tinyint(1)   | YES  |     | 0       |                |
| createdOn      | datetime     | NO   |     | NULL    |                |
| createdBy      | bigint(20)   | NO   |     | NULL    |                |
| encryptedEmail | varchar(255) | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+---------

Currently, there is no data in encryptedemail.

I wanted to create a unique key - (clientId, profileId, encryptedEmail)

So I tried converting encrypted email to default null as there were records where

clientId and profileId were duplicate.

Query I used -

 ALTER TABLE AlternateEmails MODIFY encryptedEmail varchar(255) NULL, 
 add constraint `profileId_3` 
 unique(`encryptedEmail`,`clientId`,`profileId`);

It was still showing duplicate record error.

Then I used -

 alter table AlternateEmails drop column encryptedEmail,
 add column encryptedEmail varchar(255) default NULL, add constraint 
 `profileId_3` unique(`encryptedEmail`,`clientId`,`profileId`);

It worked fine. Can Anyone explain why simply modifying column did not work?

Solution

There is a distinct difference between altering the default value on an existing field and adding a field with that new default value: if you alter the default value of an existing field, then it does not modify the already existing data that had the old default value.

The new default value is applied going forward.

This is described in MySQL's documentation on alter table statement:

Alterations that modify only table metadata and not table data are immediate because the server only needs to alter the table .frm file, not touch table contents. The following changes are made in this way:

◾ ...

◾ Changing the default value of a column (except for NDB tables).

...

So, if encryptedEmail field's default value was not null, then it had a blank string (or anything else you specified) as the default value and all the already existing records were populated with this default value. Changing the default value to null did not affect the already existing records, therefore encryptedEmail field values will be duplicates of each other.

However, when you dropped and re-created the encryptedEmail field with null as default value, MySQL populated the existing records with null values, which are not considered duplicates in MySQL.