3

I'm on Drupal 7. The table index structure of users table is shown in the fig below:

snap
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?

  • My general thinking is as follows: Did Drupal do this to my database? I'd best leave it alone. – Chapabu Oct 23 '12 at 15:30
  • 2
    Somewhat related question: drupal.stackexchange.com/questions/4777/… – mpdonadio Oct 23 '12 at 15:42
  • @Chapabu yes, obviously Drupal did this to my database. But, I was thinking about the effects(adverse) it could have if I change it. I think I'll try that on my local and test it for some time and then take my decision – AjitS Oct 25 '12 at 9:44
  • @MPD thanks for the link! It helped me clarify more. – AjitS Oct 25 '12 at 9:45
2

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) > 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 !!!

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.