Uniqueness of email addresses not enforced in artefact_internal_profile_email

[François Marier]

The uniqueness of email addresses is not enforced by the database in the artefact_internal_profile_email table.

We should consider adding a unique constraint on that column because elsewhere we prevent users from adding an email address already claimed by another user.

[François Marier (fmarier)]

Note: this is not a straightforward task. It requires investigating how we use this table in order to determine whether or not we should be changing this.

[Robert Lyon (robertl-9)]

Still no unique constraint on the email field.

[Kristina Hoeppner (kris-hoeppner)]

Which email address field is that? I thought that the user could not add another email address that is already in use through the interface?

We do get duplicate email addresses when an external authentication method is used or a user is imported via Leap2A.

[Aaron Wells (u-aaronw)]

Hi Kristina,

The field artefact_internal_profile_email.email is the database column where email artefacts get stored. If there are legitimate reasons for more than one user to have the same email address, then we should *not* put a uniqueness constraint on that column.

(A note for future developers looking at this: emails are also redundantly stored in the usr.email column. The email there is the user's "primary email address", which Mahara uses when it needs to send then an email. The other email addresses are mostly just for displaying in Contact Info & Profile blocks.)

Cheers,
Aaron

[Kristina Hoeppner (kris-hoeppner)]

The only reason why two users would have the same email address is when a Leap2A import is made or if the same user is added a second time through an external authentication method. In both cases it would be better I think if the system alerted the admin to the fact so that a duplicate account could be avoided. With the interactive import of a Leap2A file, a user doesn't have to create a new account anymore to get their content into Mahara, but content can be imported directly into an existing portfolio. And when a user tries to set up a new account with the same email through an external auth method, it would be good to alert as well because then rather than having two accounts (or more) and getting confused which one to use, the admin would be able to set up the proper auth method for the one account the user should be having.

Since a user can discern which content to make available to whom, two accounts or more shouldn't be necessary.