CODEWITHSUNDEEP
mysqlsql
MrD
MrD

Reputation: 5086

SQL enforce 1:1 relation

I have a question regarding database modelling.

Let's assume I have a table users:

users
-------
id:int
name:text

And one user_emails:

user_emails
-----------
id:int
user_id:int
email:text

I add a foreign key constraint on user_emails.user_id such that it must match a valid entry in users.id.

However, this does not guarantee that each user will have an e-mail.

I know at an application level I can easily enforce this (Ie: By validating that an 'email' field in a form is populated, etc.) but I was wondering, at a database (sql) level is there a way of enforcing the completeness of data? (That is, in this case, that each user has an email?)

Thanks! :)

Upvotes: 1

Views: 387

Answers (2)

deterministicFail
deterministicFail

Reputation: 1285

You have to turn your fk around

users
-----
id:int
name:text
user_emails_id:int (NOT NULL)

user_emails
-----
id:int
email:text (NOT NULL)

this way you force to have a relation in the email-table and if you set the email value not null, then you force to have at least an entry in the column

Edit based on the comment: To achieve multiple emails for a user, you have to add a n:m table

users
-----
id:int
name:text
user_emails_reference_id:int (NOT NULL referencing user_email_references)

user_email_references
-----
id:int
user_email_id:int (NOT NULL Referencing user_emails)
user_id:int (NOT NULL Referencing users)

user_emails
-----
id:int
email:text (NOT NULL)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Presumably, you want to allow users to have multiple emails, but just to guarantee that there is at least one. You can come quite close with MySQL constraints by doing:

create table Users (
    UserId int not null primary key, . . .
    . . .
    PrimaryEmailId int not null,
    constraint fk_PrimaryEmailId foreign key (UserId, PrimaryEmailId) references UserEmails(UserId, UserEmailId)
);

create table UserEmails (
    UserEmailId int not null primary key,
    UserId int not null,
    . . .,
    unique (UserId, UserEmailId),
    constraint fk_UserEmails_UserId foreign key (UserId) references Users(UserId)
);

This guarantees:

  • Every user has exactly one primary email.
  • Every email has one user.
  • The primary email has the same user as the one specified.

Note the use of the funky foreign key relationship. It would be sufficient to reference UserEmailId. However, that would not guarantee that the user is the same.

You might also want to add a unique constraint to the email itself, so different users cannot share a single email.

Upvotes: 1

Related Questions