Enforcing that a user should have one or more email addresses

constraintdatabase-designrelations

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint.

The tables so far are simple:

Table users:

id | password |
--------------------------------------

Table email_addresses:

user | email |
--------------------------------------

However, in the current situation, a user can also have zero email addresses, thereby preventing the user from authenticating oneself.

What's the best way to deal with this type of constraint?

Best Answer

Add a column to the users table; email_primary. Prevent the new column from accepting NULL values.

When you want to authenticate a user, simply use a UNION query, similar to:

SELECT id, email_primary
FROM users
UNION ALL
SELECT user, email
FROM email_addresses;
Related Question