Change the database schema for the User entity and add a unique key for the email

My personal take on this would be to only allow unique eMailadresses. One account per eMailadress.

The only people I could think of that need more than one account, would be admins. Those people I expect to have more than one eMailadress by hand anyway.

So I don't see a reason to allow multiaccounts per eMailadress.

But if it is easier to change everything to support multiple accounts per eMailadress, then I'm fine with that.

I don't think this feature will made into core. You could use contrib modules like Shared email http://drupal.org/project/sharedemail to achieve it.

Edit: ..and this is not a bug perhaps feature request.

This is definitely wonky. We should either allow duplicate emails or not. We've got the worst of both worlds right now.

Thanks for your work on the patches above, Christian.
I think you've demonstrated the tip of the iceberg in terms of how major a change supporting duplicate emails would be.
Further, offering configurable uniqueness of email will be even more work.

I agree with drewish - we're currently in the worst of both worlds.
I will cast my vote for disallowing duplicate emails explicitly, and enforcing uniqueness of email in the database.
Here's how I usually hack core on my own sites:

diff --git a/core/modules/user/user.install b/core/modules/user/user.install
--- a/core/modules/user/user.install
+++ b/core/modules/user/user.install
@@ -237,6 +237,7 @@
     ),
     'unique keys' => array(
       'name' => array('name'),
+      'mail' => array('mail'),
     ),
     'primary key' => array('uid'),
     'foreign keys' => array(

This patch is 100% sufficient for new sites, but of course any installs with existing dupes will fail updates.
Failing updates is a big deal, and we've painted ourselves into a corner by being wishy-washy about email for the past 10 years.
But better late than never.

From a UX perspective, I can give you an example of how terrible a system is that allows duplicate emails.
You may not be familiar with this amazon.com "feature", but their user system validates uniqueness on a dual key: email+password.
The other day I logged into my amazon account with my email address and one of my throwaway common passwords, only to find that I had no order history even though I had recently placed a new order.
Further, if I had placed orders under both accounts, there is no way to merge the two accounts.
I'd have to explicitly request one account to be deleted, and I'd lose all my order history.
Awful awful UX.

If Drupal were to fully support non-unique email, we'd have to take a similar approach, which implies some pretty fundamental shifts to any subsystem that deals with users.

It's not tricky to enforce uniqueness: just add a unique key to {users} and throw an exception when someone breaks it (even an external source).

Right now, core assumes and requires uniqueness of email without actually enforcing it.
This is broken.
And it leads to inconsistencies in the database, and a completely broken UX in which a user cannot update their own user record.

In my opinion, accommodating a non-unique external identifier is not something core should be concerned with.
In general, I think scenarios like the one you're describing need to be handled in contrib.

I have to agree with aaronbauman. We already require a unique email address - if you go to /admin/people/create and use the GUI to try creating a user with a taken email and you get the message "The email address %mail is already taken".

Let's start enforcing...

Dont many (most?) external login contrib modules simply create a ghost user in the Drupal users table with a username of some-module-user1 and some throw-away email address? This is not to say that all modules do this, but certainly some do (IIRC fb.module does this for example) so there is a solution to this for contrib.

And cant I make the same argument you are making about making life easier on contrib by pointing to the several modules out there that allow people to log in using his/her email?

We already require a unique email address

Why do we do that?

This is a fundamental question, but it's outside the scope of this issue.
If you want to open a discussion about whether we should change the existing assumption, that belongs in a new issue.
In the meantime, we can fix the buggy behavior by enforcing this assumption.

#15: user-mail-unique-4.patch queued for re-testing.

The user entity includes a validator on the mail field for uniqueness. See \Drupal\user\Plugin\Validation\Constraint\UserMailUnique.

The argument for non-unique email addresses could be made in #286401: Make email not required for a Drupal site account

This issue is about adding a unique key for the email database field.

Drupal sets many database fields as part of a unique key, even if form validation handlers verify the entered value isn't already used. One thing doesn't exclude the other one.
The other issue is about not setting as required the email field. It simply means that an empty value for the email address would be acceptable, but this doesn't exclude the validation code may reject a value that is already present in the database. It just means the validation code needs to first check the entered value isn't empty; if it's empty, it doesn't proceed with the validation.

The code to change is in UserStorageSchema::getEntitySchema(), the method that alters the database schema for the User entity, which now contains the following code.

protected function getEntitySchema(ContentEntityTypeInterface $entity_type, $reset = FALSE) {
  $schema = parent::getEntitySchema($entity_type, $reset);
  if ($data_table = $this->storage->getDataTable()) {
    $schema[$data_table]['unique keys'] += [
      'user__name' => [
        'name',
        'langcode',
      ],
    ];
  }
  return $schema;
}

What will occur if there are duplicates when the schema change is implemented?

Tried re-rolling patch against 9.4.x

Fixed cSpell error.

Re-rolled patch against #31 in Drupal 9.5.x

fixed #34 drupalPost() error

Reroll the patch #37 with Drupal 10.1.x

+    $edit['pass[pass1]'] = $new_pass = $this->randomName();
+    $edit['pass[pass2]'] = $new_pass;

randomName() isn't defined in the test class or its parent classes.

+    // Allow multiple accounts to use the same e-mail address.
+    variable_set('user_mail_unique', FALSE);

variable_set() is a Drupal 7 function.

Try to fixed #39 Cc Failed

Fixed #41 Cc Failed.

+    $random_generator = new Random();
 
     $edit = [];

The UserRegistrationTest class has the randomMachineName() and the randomString() methods. Using the Random class should not be necessary.

I still stand by my comment from #23.

I dont think we should be enforcing database level uniqueness, defer to validators and contrib to solve these issues.

Shall we get a framework maintainer opinion on this? (currently: Moshe)

I think there are still some unresolved questions, for example the one from #27:

What will occur if there are duplicates when the schema change is implemented?

It is also important to think about case-sensitivity. In MySQL unique columns are case insensitive by default, so you cannot have test@test.com and TEST@test.com. On the other side, in PostgreSQL, these are case sensitive and by default it will allow test@test.com and TEST@test.com in the unique columns. This can cause another confusion if not handled properly.

In my opinion, the uniqueness validation could remain only in code. There are lot of contrib modules creating weird email addresses just to workaround the need of the email address, or even saving empty email addresses and this change could potentially break all these.

Should we get rid of other database constraints as well then?
How do we justify having unique constraints for UUID or username, but not email?

Seems like maybe we need to define a decision making criteria for when database constraints are warranted.

Hi all - user module maintainer here.

If I were designing Drupal from scratch, I would certainly put a uniqueness constraint in the DB. But we are now 22 years into Drupal's life, and I think that ship has sailed. We dont want to disrupt existing sites so much. Folks who want to add this constraint to their DB are free to do so via schema alter. See UserStorageSchema::getEntitySchema().

Lets focus improving our add/edit validators as needed. And lets do that in other issues.

I respectfully close this long-standing issue.

I think this functionality would be great for a contrib module.