Change the index structure of fields in a table in database? Wise thing to do? I'm on Drupal 7. The table index structure of users table is shown in the fig below:


As pointed out the table has two unique keys uid(also primary) and name which is the username of the user of the site. The username is displayed on the top of the user's profile.


I want the mail column to be unique, and make it non-nullable.
and remove the unique characteristics of name;


Because username can be same (my use case) and e-mail id shouldn't repeat.
Is it safe to make the change? If not what precautions do I need to take?
If you are even thinking about doing this I have two big key phrases


BACKUPS
DEV SERVER


Here is what you should do


Install MySQL on a DevServer
mysqldump the database and load it on the DevServer
Point your Drupal App at the DevServer and bang on it, and kick the tires


Once you got a good dev environment, now do this on the DevServer

SELECT mail,COUNT(1) MailCount FROM users
GROUP BY mail HAVING COUNT(1) &gt; 1;


If you get any rows coming back, there are multiple users using the same mail value

If nothing comes back, you can proceed as follows:

CREATE TABLE users_new LIKE users;
ALTER TABLE users_new DROP INDEX `name`;
ALTER TABLE users_new DROP INDEX `mail`;
ALTER TABLE users_new ADD INDEX `name`;
ALTER TABLE users_new ADD UNIQUE INDEX `mail`;
INSERT INTO users_new SELECT * FROM users;
SELECT COUNT(1) UsersCount FROM users;
SELECT COUNT(1) UsersNewCount FROM users_new;


If UsersCount = UsersNewCount, CONGRATULATIONS, you can continue.

ALTER TABLE users RENAME users_old;
ALTER TABLE users_new RENAME users;


You drop users_old if you are sure you no longer need.

Give it a Try !!!