How to add the email uniqueness constraint

When we upgrade edx-platform from Django 1.4 to Django 1.8 in November of 2015, we lost a SQL constraint from the code.  

The constraint served two purposes: to ensure that users' emails were unique, and to make finding a user by email a fast operation.  The LMS has extra logic to prevent duplicate emails, so it is most likely that you don't need the constraint to enforce uniqueness.  We recommend you add the constraint to guarantee uniqueness, and to speed lookups.

Dogwood and Eucalyptus installations will need fixing.  We added the constraint back to the code, so Ficus installations and beyond are fine.

Step-by-step guide

  1. Log in to your installation, and prepare to run SQL commands against your MySQL database. If you are unsure how to do this, see: How do I run MySQL commands?

  2. First check if you already have the constraint using this command: 

    mysql> select * from information_schema.table_constraints where table_name = 'auth_user' and constraint_name = 'email';
  3. The result will indicate whether you have the constraint or not.
    1. This means you have the constraint, and don't have to do anything more: 

      mysql> select * from information_schema.table_constraints where table_name = 'auth_user' and constraint_name = 'email';
      +--------------------+-------------------+-----------------+--------------+------------+-----------------+
      | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
      +--------------------+-------------------+-----------------+--------------+------------+-----------------+
      | def                | edxapp            | email           | edxapp       | auth_user  | UNIQUE          |
      +--------------------+-------------------+-----------------+--------------+------------+-----------------+
      1 row in set (0.00 sec)
    2. This means you don't have the constraint, and need to create it: 

      mysql> select * from information_schema.table_constraints where table_name = 'auth_user' and constraint_name = 'email';
      Empty set (0.00 sec)
  4. To create the constraint, you run another SQL command: 

    mysql> create unique index email on auth_user (email);

    It is possible but unlikely that this statement will fail because you have duplicate emails in the auth_user table.  You will need to resolve those conflicts manually before you can create the constraint.
      

  5. To check that the constraint was created successfully, you can run the select statement from step 2 again.